Range Reference

bkeeper

New Member
Joined
Oct 12, 2005
Messages
21
I am trying to write a macro that will select a changing range. I am trying to use the Range("A1:B2") command, but the ending range can be a different column. I have used the xlToLeft and Activecell.Offset commands to find my last column, but don't know how to reference it in the Range formula. The code errors off at Range("D125:'lastcol'").select

Sample of my code:

Dim lastcol As Long

Range("IV138").Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(0, -1).Select

lastcol = ActiveCell.FormulaR1C1

Range("D125:'lastcol'").Select
Selection.ClearContents

Thanks in advance for all the help.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
What range do you actually want to select?
Code:
Dim rng As Range
    
    Set rng = Range("A1", Range("A" & Rows.Count).End(xlUp).End(xlToRight))
    rng.ClearContents
 
Upvote 0
What range do you actually want to select?
Code:
Dim rng As Range
    
    Set rng = Range("A1", Range("A" & Rows.Count).End(xlUp).End(xlToRight))
    rng.ClearContents
Hi,

try this
Code:
Range(Cells(125, 4), Cells(136, lastcol)).ClearContents
do rows 125 and 138 change also ?
then you will need to be more specific
take a look at UsedRange and CurrentRegion in the VBA-helpfiles

kind regards,
Erik
 
Upvote 0
I am wanting to find the range of cells that start at D125 and end on row 138. The column will vary depending on if I have data in the column or not. To add to my problem is that the last column with data is actually a summation column. I don't want to include this in the range selection, but rather one column left.

In summary, I am trying to write code to highlight a range of cells and delete it. The column variable will change, but the rows will not.

Thanks.
 
Upvote 0
Erik,

The rows will not change, but my code does not assign any value to "lastcol" variable.

I think your formula will work, but only if I can assign the cell reference to the last column.

Thanks.
 
Upvote 0
To assign to lastcol try this.
Code:
lastcol = Range("IV125").End(xlToLeft).Column
 
Upvote 0
Thanks everyone for all the help. Looking at everyone's help, all I needed was two lines of code to make this work.

lastcol = (Range("IV125").End(xlToLeft).Column) - 1
Range(Cells(125, 4), Cells(138, lastcol)).ClearContents

Now I can reseed the spots I been pulling my hair out on this.

Tremendous Thanks to all that have helped.

:bow:
 
Upvote 0

Forum statistics

Threads
1,224,394
Messages
6,178,342
Members
452,841
Latest member
GenAkaman

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
Back
Top