Selecting between two ranges

  • Thread starter Thread starter johnyeldham
  • Start date Start date
J

johnyeldham

I have done some very large programs in VB, but I have always had
trouble with Excel objects.

I want to clear rows on a spreadsheet - from a named range row and the
last used row on the spreadsheet.

My attempt is like this:

OutRow = .Range("MonteCarloPaste").Row
Range(.Cells(OutRow, 1), .Cells(OutRow, 1).SpecialCells
(xlCellTypeLastCell)).ClearContents

(It is inside a With statement referring to the sheet in question.)

Even the following doesn't work:

Range(.Range("A6"), .Range("A6").SpecialCells
(xlCellTypeLastCell)).ClearContents

Annoyingly, the following does work fine:

..Cells(OutRow, 1).SpecialCells(xlCellTypeLastCell).Value

What I am doing wrong?

Thanks for any help in advance!
 
I have done some very large programs in VB, but I have always had
trouble with Excel objects.

I want to clear rows on a spreadsheet - from a named range row and the
last used row on the spreadsheet.

My attempt is like this:

OutRow = .Range("MonteCarloPaste").Row
 Range(.Cells(OutRow, 1), .Cells(OutRow, 1).SpecialCells
(xlCellTypeLastCell)).ClearContents

(It is inside a With statement referring to the sheet in question.)

Even the following doesn't work:

Range(.Range("A6"), .Range("A6").SpecialCells
(xlCellTypeLastCell)).ClearContents

Annoyingly, the following does work fine:

.Cells(OutRow, 1).SpecialCells(xlCellTypeLastCell).Value

What I am doing wrong?

Thanks for any help in advance!
Click to expand...

Johny Eldham,

Since your code is inside a With statement, you need to qualify your
range, i.e. you are missing a "." before Range(.Range("A6"), .Range
("A6").SpecialCells(xlCellTypeLastCell)).ClearContents. It should
read ".Range(.Range("A6"), .Range("A6").SpecialCells
(xlCellTypeLastCell)).ClearContents". ".Cells(OutRow, 1).SpecialCells
(xlCellTypeLastCell).Value" works because you qualified it with a "."
before "Cells".

Best,

Matthew Herbert
 
Johny Eldham,

Since your code is inside a With statement, you need to qualify your
range, i.e. you are missing a "." before Range(.Range("A6"), .Range
("A6").SpecialCells(xlCellTypeLastCell)).ClearContents.  It should
read ".Range(.Range("A6"), .Range("A6").SpecialCells
(xlCellTypeLastCell)).ClearContents".  ".Cells(OutRow, 1).SpecialCells
(xlCellTypeLastCell).Value" works because you qualified it with a "."
before "Cells".

Best,

Matthew Herbert
Click to expand...

Yes, that worked! Thanks a lot!
 
Back
Top