Solved: Selecting last row of an active column using a single macro

[bananatang]

HI,

I am looking to have a single macro button that will find the last row in an active column and after finding the last column to select the whole range.

Some conditions which are important to incorporate into the code.

1. Not all the columns will have the same row of data. i.e. Column A may have 100 rows of data. Column B may only have 50 rows of data.
2. There maybe some blank rows in the selected column, and i need to find the last populated row of that active column.

Any assistance anyone can provide would be greatly apprecaited.

BT

[JONvdHeyden]

Hello BT

When you say you want to select the range, what is it? Do you mean the row? And why do you want to select it; what are you planning on doing after you select it?

The following:

Cells(Rows.Count, ActiveCell.Column).End(xlUp).EntireRow.Select

...will find the last occupied celll in the active cell column and select the entire row.

or perhaps you want to select the entire surrounding region from that cell?

Cells(Rows.Count, ActiveCell.Column).End(xlUp).CurrentRegion.Select

[bananatang]

Hello BT

When you say you want to select the range, what is it? Do you mean the row? And why do you want to select it; what are you planning on doing after you select it?

The following:

Cells(Rows.Count, ActiveCell.Column).End(xlUp).EntireRow.Select

...will find the last occupied celll in the active cell column and select the entire row.

or perhaps you want to select the entire surrounding region from that cell?

Cells(Rows.Count, ActiveCell.Column).End(xlUp).CurrentRegion.Select

HI,

My apologies, i should have made it clear. When the last row has been found in an active column. I want the whole range from the column to be selected. i.e The last row in Column A is A100. I want the macro to select A1:A100, if Column B last row of data is 50, i want the macro to select the range in column B B1:B50.

What i wanted to do with the data is to copy and pasta the data in another worksheet

Hope this helps.

BT

[JONvdHeyden]

Ok perhaps something like this then:

Cells(1, ActiveCell.Column).Resize(Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row, 1).Copy
    Sheets("Sheet2").Range("A1").PasteSpecial xlValues

I have opted for the pastspecial command here, you could just drop the xlValues if you want to paste everything.

Also, change destination sheet name and range to suit.

Note it's not necessary to select the data, notice how I have called copy directly without selecting the range first.

Hope this helps.

[bananatang]

Hi Jon,

Your code works a treat.

Thanks for the code and for the tips.

Many Thanks

BT