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
(E-Mail Removed)
"Emma Hope" <(E-Mail Removed)> wrote in message
news:22CBF803-725D-4F37-BA88-(E-Mail Removed)...
>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
> > >