Change selection to last row and last column

[ExcelNoob222]

Hi,

I have a workbook that goes from column A to J. Previously I would copy B to J from one worksheet to another use the below code:

Dim LastRow As Long
LastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Range("B10:J" & LastRow).Copy

However going forward, J will not always be the last column. Sometimes it may be M or P etc. So I wondering if there is something similar to "LastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row" for columns and if my "Range("B10:J" & LastRow).Copy" code can be modified where J is replaced with the last column?

Thanks!

 

[Joe4]

First, pick a row that will always have an entry in that last column (whatever it is). Let's say its row 10.

Then, you can find the last column like this:

Dim LastColumn as Long
LastColumn = Worksheets("Sheet1").Cells(10, Columns.Count).End(xlToLeft).Column

and then you can combine both, and copy your dynamic range like this:

Range(Cells(10,"B"),Cells(LastRow,LastColumn)).Copy

 

[steve the fish]

Like this for last column in row 1:

lc = Cells(1, Columns.Count).End(xlToLeft).Column

 

[Joe4]

Additional note. If you do not have any totally blank rows or columns in the middle of your data, you can do everything (including the copy) in a single line of code like this:

Range("B10").CurrentRegion.Copy

 

[ExcelNoob222]

First, pick a row that will always have an entry in that last column (whatever it is). Let's say its row 10.

Then, you can find the last column like this:

Dim LastColumn as Long
LastColumn = Worksheets("Sheet1").Cells(10, Columns.Count).End(xlToLeft).Column

and then you can combine both, and copy your dynamic range like this:

Range(Cells(10,"B"),Cells(LastRow,LastColumn)).Copy

Hi! This mostly worked! I unfortunately left this part out of my original message, that I only want to grab up until the first blank header. So for example, I have headers in column A to M, then N and O is blank and then P-R have headers. so I want to grab B10:M. The above suggestion grabs all the way to R.

Additional note. If you do not have any totally blank rows or columns in the middle of your data, you can do everything (including the copy) in a single line of code like this:

Range("B10").CurrentRegion.Copy

I tried this but this code grabs everything so columns A and rows 1-9.

 

[Joe4]

Hi! This mostly worked! I unfortunately left this part out of my original message, that I only want to grab up until the first blank header. So for example, I have headers in column A to M, then N and O is blank and then P-R have headers. so I want to grab B10:M. The above suggestion grabs all the way to R.

Yes, that is an important detail.
You can come from the other direction like this:

Dim LastColumn as Long
LastColumn = Worksheets("Sheet1").Range("B10").End(xlToRight).Column

 

[steve the fish]

Well as you asked for the last column you would get replies for the last column! Try this presuming your headers start in A1:

If Cells(1, 2) <> "" Then
    lc = Cells(1, 1).End(xlToRight).Column
Else
    lc = 1
End If

 

[steve the fish]

The if test is there in case you ever have just one header. If that is NEVER going to happen then its overkill. I prefer overkill as it stops pain later.

 

[ExcelNoob222]

LastColumn = Worksheets("Sheet1").Range("B10").End(xlToRight).Column

Great thank you!

 

[ExcelNoob222]

Well as you asked for the last column you would get replies for the last column! Try this presuming your headers start in A1:

If Cells(1, 2) <> "" Then
    lc = Cells(1, 1).End(xlToRight).Column
Else
    lc = 1
End If

Yea, me missing a key detail really changes the resutls! haha my bad. Your initial reply worked but caused the same issue as Joe4. I tried your new suggestion as well and it worked too. Thanks for the help!