Selecting between two ranges
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! |
Selecting between two ranges
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 |
Selecting between two ranges
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! |
All times are GMT +1. The time now is 05:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com