Change selection to last row and last column

ExcelNoob222

New Member
Joined
Jun 17, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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:

VBA 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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,960
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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:
VBA Code:
Range(Cells(10,"B"),Cells(LastRow,LastColumn)).Copy
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
Like this for last column in row 1:

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,960
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
Range("B10").CurrentRegion.Copy
 

ExcelNoob222

New Member
Joined
Jun 17, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
VBA Code:
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:
VBA Code:
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:
VBA Code:
Range("B10").CurrentRegion.Copy

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,960
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
Dim LastColumn as Long
LastColumn = Worksheets("Sheet1").Range("B10").End(xlToRight).Column
 
Solution

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 17, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Well as you asked for the last column you would get replies for the last column! Try this presuming your headers start in A1:

VBA Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,827
Messages
5,638,559
Members
417,036
Latest member
Sahil2812

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top