Select range using cell reference (VBA)

ianto2842

New Member
Joined
Apr 7, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
I am trying to get VBA to select a range of cells to copy based on a value in a given cell. The range always starts in the same place, same columns, but down to a particular row depending on the data. I cant use xlup as it isn't the last row and there are intentionally blank rows plus rows that appear blank but have formulae in them - so aren't seen as blank.

I need it to be able to select the area C3:J(value in B3) of the current sheet. Any help would be greatly appreciated! Using 2013.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Is the value in B3 unique?
Then you can use MATCH to find the row.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
Dim rng As Range
Dim LastRow As Long

    ' get last row from B3
    LastRow = Range("B3").Value

    ' set rng to refer to required range
    Set rng = Range("C3:J" & LastRow)

    ' do stuff with rng
    rng.Select

    rng.Copy
 
Solution

ianto2842

New Member
Joined
Apr 7, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Try this.
VBA Code:
Dim rng As Range
Dim LastRow As Long

    ' get last row from B3
    LastRow = Range("B3").Value

    ' set rng to refer to required range
    Set rng = Range("C3:J" & LastRow)

    ' do stuff with rng
    rng.Select

    rng.Copy
That's brilliant, does exactly what I need! Thank you :D
 

Watch MrExcel Video

Forum statistics

Threads
1,129,940
Messages
5,639,101
Members
417,072
Latest member
JaimeDee

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