i have a named range RowCountPlusOne which holds a number i.e. i want to use
VBA to clear the cells after a certain number of rows i.e. row 34 to row 1000.

The code below just errors, can anyone else with what i am doing wrong.

Sheets("Breakdown").Select
strSelection = "$A$" & Range("RowCountPlusOne") & ":$BZ$1000"
Range(strSelection).Select
Selection.Clear
Range("A1:A2").Select

Thanks
Emma

Maybe change this:

strSelection = "$A$" & Range("RowCountPlusOne") & ":$BZ$1000"

To this
strSelection = Range("$A$" & RowCountPlusOne & ":$BZ$1000"



"Emma Hope" wrote:

i have a named range RowCountPlusOne which holds a number i.e. i want to use
VBA to clear the cells after a certain number of rows i.e. row 34 to row 1000.

The code below just errors, can anyone else with what i am doing wrong.

Sheets("Breakdown").Select
strSelection = "$A$" & Range("RowCountPlusOne") & ":$BZ$1000"
Range(strSelection).Select
Selection.Clear
Range("A1:A2").Select

Thanks
Emma

Nope, still getting the same error message

Run time error '1004' - application-defined or object-defined error






"JLGWhiz" wrote:

Maybe change this:

strSelection = "$A$" & Range("RowCountPlusOne") & ":$BZ$1000"

To this
strSelection = Range("$A$" & RowCountPlusOne & ":$BZ$1000"



"Emma Hope" wrote:

i have a named range RowCountPlusOne which holds a number i.e. i want to use
VBA to clear the cells after a certain number of rows i.e. row 34 to row 1000.

The code below just errors, can anyone else with what i am doing wrong.

Sheets("Breakdown").Select
strSelection = "$A$" & Range("RowCountPlusOne") & ":$BZ$1000"
Range(strSelection).Select
Selection.Clear
Range("A1:A2").Select

Thanks
Emma

Is your code in a General module or behind a worksheet?

Sheets("Breakdown").Select
strSelection _
= "A" & sheets("breakdown").Range("RowCountPlusOne").Value & ":BZ1000"
sheets("breakdown").Range(strSelection).Select
Selection.Clear
Sheets("Breakdown").Range("A1:A2").Select

or to clear the range:
with sheets("breakdown")
.range("A" & .Range("RowCountPlusOne").Value & ":BZ1000").clear
end with

You don't usually need to select a worksheet or range to work with it. And you
don't need the $'s in the range address in your code.

Emma Hope wrote:

i have a named range RowCountPlusOne which holds a number i.e. i want to use
VBA to clear the cells after a certain number of rows i.e. row 34 to row 1000.

The code below just errors, can anyone else with what i am doing wrong.

Sheets("Breakdown").Select
strSelection = "$A$" & Range("RowCountPlusOne") & ":$BZ$1000"
Range(strSelection).Select
Selection.Clear
Range("A1:A2").Select

Thanks
Emma

--

Dave Peterson

This should work from anywhere in the workbook with NO selections necessary
or desirable

sub clearrowsinothersheet
Sheets("Breakdown").Range("$a" & Range("RowCountplusone") &
":bz1000").clearcontents
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Emma Hope" wrote in message
...
i have a named range RowCountPlusOne which holds a number i.e. i want to
use
VBA to clear the cells after a certain number of rows i.e. row 34 to row
1000.

The code below just errors, can anyone else with what i am doing wrong.

Sheets("Breakdown").Select
strSelection = "$A$" & Range("RowCountPlusOne") & ":$BZ$1000"
Range(strSelection).Select
Selection.Clear
Range("A1:A2").Select

Thanks
Emma

Probably because I forgot to put the Parenth after the last cell refrence.

strSelection = Range("$A$" & RowCountPlusOne & ":$BZ$1000")

I assumed the "RowCountPlusOne" is a variable that equates to an integer
value. If not, then this still would not work. The entire Range statement
has to have a value that looks like: Range("$A$2:$BZ$1000")
or it will give you that error message. That is why RowCountPusOne has to
equal a number.


"Emma Hope" wrote:

Nope, still getting the same error message

Run time error '1004' - application-defined or object-defined error






"JLGWhiz" wrote:

Maybe change this:

strSelection = "$A$" & Range("RowCountPlusOne") & ":$BZ$1000"

To this
strSelection = Range("$A$" & RowCountPlusOne & ":$BZ$1000"



"Emma Hope" wrote:

i have a named range RowCountPlusOne which holds a number i.e. i want to use
VBA to clear the cells after a certain number of rows i.e. row 34 to row 1000.

The code below just errors, can anyone else with what i am doing wrong.

Sheets("Breakdown").Select
strSelection = "$A$" & Range("RowCountPlusOne") & ":$BZ$1000"
Range(strSelection).Select
Selection.Clear
Range("A1:A2").Select

Thanks
Emma