Range Reference

[bkeeper]

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.

 

[Norie]

What range do you actually want to select?

Dim rng As Range
    
    Set rng = Range("A1", Range("A" & Rows.Count).End(xlUp).End(xlToRight))
    rng.ClearContents

 

[erik.van.geit]

What range do you actually want to select?

Dim rng As Range
    
    Set rng = Range("A1", Range("A" & Rows.Count).End(xlUp).End(xlToRight))
    rng.ClearContents

Hi,

try this

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

 

[bkeeper]

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.

 

[erik.van.geit]

tweaking a little my code will do then

Range(Cells(125, 4), Cells(136, lastcol -1)).ClearContents

just added "-1" to clear to the column before the last one

 

[bkeeper]

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.

 

[Norie]

To assign to lastcol try this.

lastcol = Range("IV125").End(xlToLeft).Column

 

[bkeeper]

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.

 

[erik.van.geit]

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

nevermind, when you join DRAFT, we will accept you the way you are