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.

 

[shawrod]

You are correct . I meant the first empty cell after last non empty cell.

 

[stefankemp]

Range("E" & Cells.Rows.Count).End(xlUp).Offset(1,0).Select

 

[shawrod]

This did the job! Thank you so much!

 

[Kamolga]

What if I want to select the last empty cell?

For the last empty cell in the set of data

Range("A" & Cells.Rows.Count).End(xlUp).End(xlUp).Offset(-1, 0).Select

 

[Scott Huish]

You're responding to an old thread, but you shouldn't need two .End(xlUp)

 

[Kamolga]

Yes sorry and I realized there was a page 2 after posting.

If you take a .End(xlUp) off, you would just get the cell above the last cell...and by having a second, you get to the cell Under the first empty cell (as long as the cell above the last cell is not empty else you would already be at target)

 

[erbhaskar95]

What would be the code to select the cell which is just below the last used cell in a particular column?

 

[Fluff]

How about

Range("A" & Rows.Count).End(xlUp).Offset(1).Select

 

[erbhaskar95]

Thank you very much @Fluff. This worked like a charm. However, I got stuck in one similar problem as this one.

Lets say I have my main excel workbook that contains 2800 rows (filled with data). I copied some information or range of rows from another workbook and pasted it in my main excel workbook. The pasted range was upto 1500 rows only. Now how can I delete the range from one cell below the last pasted cell row (i.e 1501 in this case) to 2800 rows? Sometimes it might be 1400 rows or 1700 rows depending on situations so my main objective is to delete the rows from one cell below the last pasted range upto the end (i.e row number 2800)

 

[Fluff]

As this is a totally different you need to start a new thread. Thanks