 |
\r\n\r\n\r\nHello,
\r\n
\r\nI am wondering if someone could help me with a question. I am using VBA to count total rows in a spreadsheet, and upon find the MAX number, returning the last row\'s data. The dilemma is, however, that this data source will continually grow in size. The static method is OK if you always know the range, but it later dawned on me that this is a test source only; production data will be CONSIDERABLY larger. I am toying with the idea of using a dynamic array to do this. I am pretty new to VBA and thought I\'d ask for some input. The code below is pretty self explanatory. I am using relative references to do this below.
\r\n
\r\nAppreciate any help you can provide!
\r\n
\r\n__________________________________________________
\r\n
\r\nowb.Worksheets("tester").Select
\r\n With ActiveSheet.Range("A1:H500")
\r\n .Select
\r\n Worksheets.Add
\r\n Worksheets("Sheet1").Range("A1").Cells.Value = "Number of rows from A"
\r\n Worksheets("sheet1").Range("B1").Cells.Value = "=COUNT(tester!R[1]C[-1]:R[500]C[-1])"
\r\n Worksheets("Sheet1").Range("A2").Cells.Value = "Number of rows from B"
\r\n Worksheets("sheet1").Range("B2").Cells.Value = "=COUNT(tester!RC:R[499]C)"
\r\n Worksheets("Sheet1").Range("A3").Cells.Value = "Number of rows from C"
\r\n Worksheets("sheet1").Range("B3").Cells.Value = "=COUNT(tester!R[-1]C[1]:R[498]C[1])"
\r\n Worksheets("Sheet1").Range("A4").Cells.Value = "Number of rows from D"
\r\n Worksheets("sheet1").Range("B4").Cells.Value = "=COUNT(tester!R[-2]C[2]:R[497]C[2])"
\r\n Worksheets("Sheet1").Range("A5").Cells.Value = "Number of rows from E"
\r\n Worksheets("sheet1").Range("B5").Cells.Value = "=COUNT(tester!R[-3]C[3]:R[496]C[3])"
\r\n Worksheets("Sheet1").Range("A6").Cells.Value = "Number of rows from F"
\r\n Worksheets("sheet1").Range("B6").Cells.Value = "=COUNT(tester!R[-4]C[4]:R[495]C[4])"
\r\n Worksheets("Sheet1").Range("A7").Cells.Value = "Number of rows from G"
\r\n Worksheets("sheet1").Range("B7").Cells.Value = "=COUNT(tester!R[-5]C[5]:R[494]C[5])"
\r\n Worksheets("Sheet1").Range("A8").Cells.Value = "Number of rows from H"
\r\n Worksheets("sheet1").Range("B8").Cells.Value = "=COUNT(tester!R[-6]C[6]:R[493]C[6])"
\r\n \'Worksheets("Sheet1").Range("A9").rows.value
\r\n \'Worksheets("sheet1").Range("B9").Cells.Value = "=Max(R[-7]C:R[-2]C)"
\r\n
\n
\n \r\n \r\n\r\n | \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n\r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n  \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n \r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n';
pd[29298] = '\r\n\r\n \r\n\r\n\r\n \r\n \r\n\r\n \r\n \r\n\r\n\r\n\r\n \r\n\r\n \r\n \r\n \r\n # 2 \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n  \r\n \r\n 06-30-2001, 05:36 AM\r\n \r\n \r\n \r\n | \r\n \r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n\r\n \r\n\r\n Guest \r\n \r\n \r\n\r\n | \r\n | \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n Posts: n/a\r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n \r\n \r\n | \r\n \r\n\r\n \r\n \r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n \r\n  \r\n Re: Dynamic Arrays\r\n
\r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n\r\n \r\n\r\n\r\n Hi,
\r\n
\r\nI think you can find the last used cell in a spreadsheet with \'specialcells\'.
\r\n
\r\nDim intLastRow As Integer
\r\nintLastRow = Sheets("tester").Cells.SpecialCells(xlLastCell).Row
\r\nMsgBox intLastRow
\r\n
\r\nHope this is of use.
\n
\n \r\n \r\n\r\n | \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n\r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n \r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n\r\n\r\n \r\n\r\n \r\n \r\n \r\n # 3 \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n 07-02-2001, 01:08 PM\r\n \r\n \r\n \r\n | \r\n \r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n\r\n \r\n\r\n Guest \r\n \r\n \r\n\r\n | \r\n | \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n Posts: n/a\r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n \r\n \r\n | \r\n \r\n\r\n \r\n \r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n \r\n \r\n Re: Dynamic Arrays\r\n
\r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n\r\n \r\n\r\n\r\n Thanks Blue,
\r\n
\r\nBut I need to compare all columns as far as how many rows, then after defining that, return the entire rows data. This will ensure I have the lastest data. The spreadsheet I use will continually grow, so I need to make it dynamic with an array, I think.
\r\n
\r\n
\n
\n \r\n \r\n\r\n | \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n\r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n \r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n\r\n\r\n \r\n\r\n \r\n \r\n \r\n # 4 \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n 07-06-2001, 08:53 PM\r\n \r\n \r\n \r\n | \r\n \r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n\r\n \r\n\r\n Guest \r\n \r\n \r\n\r\n | \r\n | \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n Posts: n/a\r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n \r\n \r\n | \r\n \r\n\r\n \r\n \r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n \r\n \r\n Re: Dynamic Arrays\r\n
\r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n\r\n \r\n\r\n\r\n Hi, though I am quite new to VBA myself, but I seem to be doing the same thing very often, hope it will help.
\r\n
\r\nIf you need to count the number of rows in the sheet:
\r\n
\r\n_____________________________________________
\r\nWorksheets("Sheet1").Activate
\r\n
\r\nj=1 \'If the first non-empty cell is in the first row
\r\nDo While Not IsEmpty(Cells(j+1,1))
\r\n j=j+1
\r\nLoop
\r\nn=j
\r\nRedim MyArray(n)
\r\n________________________________________________
\r\n
\r\nBut this works only for uninterrupted vertical sequence of cells.
\r\n
\r\nHope it will help,
\r\nKarhu
\r\n
\n
\n \r\n \r\n\r\n | \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n\r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n \r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n\r\n\r\n \r\n\r\n \r\n \r\n \r\n # 5 \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n 07-09-2001, 08:47 AM\r\n \r\n \r\n \r\n | \r\n \r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n\r\n \r\n\r\n Guest \r\n \r\n \r\n\r\n | \r\n | \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n Posts: n/a\r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n \r\n \r\n | \r\n \r\n\r\n \r\n \r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n \r\n  \r\n Re: Dynamic Arrays\r\n
\r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n\r\n \r\n\r\n\r\n Thanks. Now that I have counted the rows, I need to return the last rows data. 1 row X 8 Columns is the size. Should I place all elements into the array? What I mean is, should I load columnar and return last element indexed, once size is found?
\r\n
\r\nThanks Again,
\r\n
\r\nDave
\n
\n \r\n \r\n\r\n | \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n\r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n \r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n\r\n\r\n \r\n\r\n \r\n \r\n \r\n # 6 \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n 07-11-2001, 08:59 PM\r\n \r\n \r\n \r\n | \r\n \r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n\r\n \r\n\r\n Guest \r\n \r\n \r\n\r\n | \r\n | \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n Posts: n/a\r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n \r\n \r\n | \r\n \r\n\r\n \r\n \r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n \r\n \r\n Re: Dynamic Arrays\r\n
\r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n\r\n \r\n\r\n\r\n Hi Dave,
\r\n
\r\nIf the number of columns is 8, the following will return the last row data (you know the number of rows is n)
\r\n
\r\nDim LastRowData(8) as Variant
\r\n
\r\nFor i=1 to 8 \'If the data is in the FIRST 8 columns
\r\nLastRowData(i)=Cells(n,i)
\r\nNext i
\r\n
\r\nArray LastRowData(8) will contain the values you need
\r\n
\r\nHope this will help,
\r\nKarhu
\r\n
\n
\n \r\n \r\n\r\n | \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n\r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n \r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n
06-29-2001, 02:43 PM
|
|
|
Dynamic Arrays
|
Hello,
I am wondering if someone could help me with a question. I am using VBA to count total rows in a spreadsheet, and upon find the MAX number, returning the last row's data. The dilemma is, however, that this data source will continually grow in size. The static method is OK if you always know the range, but it later dawned on me that this is a test source only; production data will be CONSIDERABLY larger. I am toying with the idea of using a dynamic array to do this. I am pretty new to VBA and thought I'd ask for some input. The code below is pretty self explanatory. I am using relative references to do this below.
Appreciate any help you can provide!
__________________________________________________
owb.Worksheets("tester").Select
With ActiveSheet.Range("A1:H500")
.Select
Worksheets.Add
Worksheets("Sheet1").Range("A1").Cells.Value = "Number of rows from A"
Worksheets("sheet1").Range("B1").Cells.Value = "=COUNT(tester!R[1]C[-1]:R[500]C[-1])"
Worksheets("Sheet1").Range("A2").Cells.Value = "Number of rows from B"
Worksheets("sheet1").Range("B2").Cells.Value = "=COUNT(tester!RC:R[499]C)"
Worksheets("Sheet1").Range("A3").Cells.Value = "Number of rows from C"
Worksheets("sheet1").Range("B3").Cells.Value = "=COUNT(tester!R[-1]C[1]:R[498]C[1])"
Worksheets("Sheet1").Range("A4").Cells.Value = "Number of rows from D"
Worksheets("sheet1").Range("B4").Cells.Value = "=COUNT(tester!R[-2]C[2]:R[497]C[2])"
Worksheets("Sheet1").Range("A5").Cells.Value = "Number of rows from E"
Worksheets("sheet1").Range("B5").Cells.Value = "=COUNT(tester!R[-3]C[3]:R[496]C[3])"
Worksheets("Sheet1").Range("A6").Cells.Value = "Number of rows from F"
Worksheets("sheet1").Range("B6").Cells.Value = "=COUNT(tester!R[-4]C[4]:R[495]C[4])"
Worksheets("Sheet1").Range("A7").Cells.Value = "Number of rows from G"
Worksheets("sheet1").Range("B7").Cells.Value = "=COUNT(tester!R[-5]C[5]:R[494]C[5])"
Worksheets("Sheet1").Range("A8").Cells.Value = "Number of rows from H"
Worksheets("sheet1").Range("B8").Cells.Value = "=COUNT(tester!R[-6]C[6]:R[493]C[6])"
'Worksheets("Sheet1").Range("A9").rows.value
'Worksheets("sheet1").Range("B9").Cells.Value = "=Max(R[-7]C:R[-2]C)"
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Threaded Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|
|
