Find last cell of data

[Striker]

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.

 

[Norman Jones]

Hi Striker,

Try:

Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

 

[Norman Jones]

Hi Striker,

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

should read:

LastRow = Cells(Rows.Count, "A").End(xlUp)(2).Row

 

[Striker]

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

 

[Striker]

Silly me, I just noticed you did that already

Sorry

 

[Norman Jones]

Hi Striker,

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

 

[Norman Jones]

Hi Striker,

I just noticed you did that already

Yes, but note that

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.

 

[Striker]

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]

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
'<<=============

 

[SMertz]

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]

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.

 

[Striker]

Sorry about that Temporary computer problems.

When you say a standard module, are you talking about where the module is
inserted.

Thanks

 

[Norman Jones]

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

 

[AD108]

Hi Norman,

What is the (2) for in this statement.

Thanks in advance,

Ariel

 

[Norman Jones]

Hi Ariel,

What is the (2) for in this statement.

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