VBA Last row copy

[jamescooper]

Range("AG2:BC1000").Copy Sheets("Data").Cells(Sheets("Data").Rows.Count, "D").End(xlUp).Offset(1, 0)

The code above copy and pastes the range specified from the current sheet to the data sheet.

To improve this I would like it to copy the range down to the last populated row but this could be in either column AG, AK, AO, AS, AW or BA.

Any idea how I could achieve this please?

 

[alansidman]

From Abdo's Post at Stack Overflow

The best way to get number of last row used is:

<code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">ActiveSheet.UsedRange.Rows.Count</code>

for the current sheet.

[COLOR=#242729][FONT=Arial]<code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Worksheets("Sheet name").UsedRange.Rows.Count</code>

for a specific sheet.[/FONT][/COLOR]
To get number of the last column used:

[COLOR=#242729][FONT=Arial]<code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">ActiveSheet.UsedRange.Columns.Count</code>

for the current sheet.[/FONT][/COLOR]

[COLOR=#242729][FONT=Arial]<code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Worksheets("Sheet name").UsedRange.Columns.Count</code>

for a specific sheet.[/FONT][/COLOR]
I hope this helps.

 

[MARK858]

@jamescooper, is there data in the other columns that could use more rows than the ones that you have specified?

 

[jamescooper]

There could be yes, hence why I have specified the columns.

 

[MARK858]

Are the columns that could be longer within the column range AG:BC or outside the range?

 

[MARK858]

Assuming that the longer columns are outside the columns stated range then see if the below gives the correct last row...

Debug.Print Columns("AG:BC").Find("*", , xlValues, , xlByRows, xlPrevious).Row