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!

On May 6, 9:56*am, "
wrote:
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!

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)).ClearCont ents. 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

On 6 May, 15:16, wrote:
On May 6, 9:56*am, "



wrote:
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!

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)).ClearCont ents. *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

Yes, that worked! Thanks a lot!