Select the last cell in a column

[zacheisjd]

I am very new to VBA and have Microsoft Office Excel 2007 Power programming with VBA in which this website was listed as a good reference site. I am working on a spreadsheet where I want to select the last non-empty cell in a column and the column always has some cells that are empty, though it is NOT the last non-empty cell in the column. This spreadsheet is automatically generated and I want to change this particular column to all one format and all one number, 0042. This spreadsheet is then loaded into another system via tab-delimited txt file, so this number is meant to serve as an indicator. Sounds like I gave too much information, just wanted to be clear. Any help is appreciated. Thanks.

 

[Jonmo1]

Welcome to the board. And that book is right, this is a GREAT reference site for excel...

Here's a common code to find the last used cell in a column. This will use column A for example...

LR = Cells(Rows.Count, "A").End(xlup).Row

LR is now a variable represinting the Row # of the last used cell in column A
Now you can use that variable in a range statement like this

Range("A1:A" & LR)

Hope that helps...

 

[Mala]

Range("E" & Cells.Rows.Count).End(xlUp).Select

will select the last non-empty cell in Col E

 

[zacheisjd]

Thank you very much!

 

[zacheisjd]

I get a run-time error 91 Object Variable or With block not set. So I am basically trying to create a macro that selects a whole column from the beginning to the last non-blank cell, but there may blank cells within the column. The end function won't work as a generic macro as the data and the total amount of rows will change from report to report. Once I get this I can continue with my formatting. Thanks again!

 

[Jonmo1]

Try this for column E

Range("E1").Resize(Cells(Rows.Count,"E").End(xlUp).Row,1).Select

 

[Scott Huish]

Note, it is not actually necessary to select, you can work with ranges directly:

Sub test()
With Range("E1:E" & Range("E" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "@"
    .Value = "0042"
End With
End Sub

 

[EDUCATED MONKEY]

thanks for this posting i just moded it to fit my needs thanks

 

[shawrod]

Range("E" & Cells.Rows.Count).End(xlUp).Select

will select the last non-empty cell in Col E

What if I want to select the last empty cell?

 

[stefankemp]

The last empty cell would be the last cell in the column, provided it is empty. Do you mean the first empty cell after the last non-empty cell?