Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
I'm looking for a way in VBA to look through a spreadsheet and find the last
cell with data in it, this way I can set a counter to loop later. Normally somewhere between 100, and 600 rows of data. So I guess I'm looking for the first cell in ROW "A" with nothing in it. Any help for a fairy new person to VBA - greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
Hi Striker,
Try: Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row --- Regards, Norman "Striker" wrote in message ... I'm looking for a way in VBA to look through a spreadsheet and find the last cell with data in it, this way I can set a counter to loop later. Normally somewhere between 100, and 600 rows of data. So I guess I'm looking for the first cell in ROW "A" with nothing in it. Any help for a fairy new person to VBA - greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
Hi Striker,
LastRow = Cells(Rows.Count, "A").End(xlUp).Row should read: LastRow = Cells(Rows.Count, "A").End(xlUp)(2).Row --- Regards, Norman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
Thanks this is great, one more question. How can I set a variable to this
number? Like Dim iCell as integer iCell = LastRow = Cells(Rows.Count, "A").End(xlUp).Row Thanks "Norman Jones" wrote in message ... Hi Striker, Try: Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row --- Regards, Norman "Striker" wrote in message ... I'm looking for a way in VBA to look through a spreadsheet and find the last cell with data in it, this way I can set a counter to loop later. Normally somewhere between 100, and 600 rows of data. So I guess I'm looking for the first cell in ROW "A" with nothing in it. Any help for a fairy new person to VBA - greatly appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
Silly me, I just noticed you did that already
Sorry "Norman Jones" wrote in message ... Hi Striker, Try: Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row --- Regards, Norman "Striker" wrote in message ... I'm looking for a way in VBA to look through a spreadsheet and find the last cell with data in it, this way I can set a counter to loop later. Normally somewhere between 100, and 600 rows of data. So I guess I'm looking for the first cell in ROW "A" with nothing in it. Any help for a fairy new person to VBA - greatly appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
Hi Striker,
Dim LastRow As Long declares a variable (LastRow) and the instruction: LastRow = Cells(Rows.Count, "A").End(xlUp)(2).Row assigns a value to the LastRow variable. You may, of course, replace LastRow with any (valid) variable name of your choice - perhaps iRow might be more intuitive than iCell. So, completely equivalent to my previous suggestion would be: Dim iRow As Long iRow = Cells(Rows.Count, "A").End(xlUp)(2).Row --- Regards, Norman "Striker" wrote in message ... Thanks this is great, one more question. How can I set a variable to this number? Like Dim iCell as integer iCell = LastRow = Cells(Rows.Count, "A").End(xlUp).Row Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
Hi Striker,
I just noticed you did that already Yes, but note that LastRow = Cells(Rows.Count, "A").End(xlUp).Row returns the last populated cell in column A, whereas LastRow = Cells(Rows.Count, "A").End(xlUp)(2).Row returns the empty cell immediately below the last populated cell. --- Regards, Norman |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
Thanks for that.
Now that i have the last row number I need to loop thru that many rows and combine cells A, C, and F into a cell G. A C F G 1234 3032109987 UNK 12343032109987UNK "Norman Jones" wrote in message ... Hi Striker, I just noticed you did that already Yes, but note that LastRow = Cells(Rows.Count, "A").End(xlUp).Row returns the last populated cell in column A, whereas LastRow = Cells(Rows.Count, "A").End(xlUp)(2).Row returns the empty cell immediately below the last populated cell. --- Regards, Norman |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
Hi Striker,
Now that i have the last row number I need to loop thru that many rows and combine cells A, C, and F into a cell G. A C F G 1234 3032109987 UNK 12343032109987UNK Try something like: '============= Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rCell As Range Dim LastRow As Long Set WB = Workbooks("YourBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Sheet2") '<<==== CHANGE LastRow = Cells(Rows.Count, "A").End(xlUp).Row Set rng = Range("A2:A" & LastRow) For Each rCell In rng.Cells With rCell .Offset(0, 3).Value = .Value _ & .Offset(0, 1).Value & .Offset(0, 2).Value End With Next rCell End Sub '<<============= --- Regards, Norman |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
GREAT, Thanks for that. I think I'll paste into sheet and step thru it to
make sure I understand it. Thanks again. "Norman Jones" wrote in message ... Hi Striker, Now that i have the last row number I need to loop thru that many rows and combine cells A, C, and F into a cell G. A C F G 1234 3032109987 UNK 12343032109987UNK Try something like: '============= Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rCell As Range Dim LastRow As Long Set WB = Workbooks("YourBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Sheet2") '<<==== CHANGE LastRow = Cells(Rows.Count, "A").End(xlUp).Row Set rng = Range("A2:A" & LastRow) For Each rCell In rng.Cells With rCell .Offset(0, 3).Value = .Value _ & .Offset(0, 1).Value & .Offset(0, 2).Value End With Next rCell End Sub '<<============= --- Regards, Norman |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
Hi Striker,
GREAT, Thanks for that. I think I'll paste into sheet and step thru it to make sure I understand it. The code should be pasted into a standard module rather than a sheet module. --- Regards, Norman |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
Sorry about that Temporary computer problems.
When you say a standard module, are you talking about where the module is inserted. Thanks "Norman Jones" wrote in message ... Hi Striker, GREAT, Thanks for that. I think I'll paste into sheet and step thru it to make sure I understand it. The code should be pasted into a standard module rather than a sheet module. --- Regards, Norman |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
Hi Striker,
When you say a standard module, are you talking about where the module is inserted. A sheet module is the module behind the worksheet and may be accessed by right-clicking the sheet's tab and selecting the View Code option. To insert a standard module: (from a workshhet) Alt-F11 to open the VBE Insert | Module For more information see Chip Pearson's Code Module page at: http://www.cpearson.com/excel/codemods.htm --- Regards, Norman |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
Hi Norman,
What is the (2) for in this statement. Thanks in advance, Ariel "Norman Jones" wrote in message ... Hi Striker, Dim LastRow As Long declares a variable (LastRow) and the instruction: LastRow = Cells(Rows.Count, "A").End(xlUp)(2).Row assigns a value to the LastRow variable. You may, of course, replace LastRow with any (valid) variable name of your choice - perhaps iRow might be more intuitive than iCell. So, completely equivalent to my previous suggestion would be: Dim iRow As Long iRow = Cells(Rows.Count, "A").End(xlUp)(2).Row --- Regards, Norman "Striker" wrote in message ... Thanks this is great, one more question. How can I set a variable to this number? Like Dim iCell as integer iCell = LastRow = Cells(Rows.Count, "A").End(xlUp).Row Thanks |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last cell of data
Hi Ariel,
What is the (2) for in this statement. LastRow = Cells(Rows.Count, "A").End(xlUp)(2).Row This line of code is an abbreviation for: LastRow = Cells(Rows.Count, "A").End(xlUp).Item(2,1).Row and is equivalent to: LastRow = Cells(Rows.Count, "A").End(xlUp).Offset(1,0).Row For a detailed discussion of this, Chip Pearson hosts an excellent article, written by Alan Beban http://www.cpearson.com/excel/cells.htm --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i find specific data in a cell and its colocated cell data | Excel Worksheet Functions | |||
how to find number in a cell , a cell contains character data ornumeric data | Excel Worksheet Functions | |||
find last cell in range with data, display cell address | Excel Worksheet Functions | |||
Find MAX data in sheet (Cell) - Any cell that exceeds X amount of data | Excel Discussion (Misc queries) | |||
The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) |