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.

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.

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

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.

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.

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

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

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

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

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

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

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

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

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

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