Error in macro that automatically inserts numbers

[boela]

Hi guys,

Please help me with the following code for automatically updating numbers when rows are inserted/deleted. VBA gives me an error on the line:

Range("A & LastCell + 1:A" & Rows.Count) = ""

The code is below...

Please Login or Register  to view this content.

Why is my synatx wrong. Should I put a .Value after the last bracket? Please take my hand and point me in the right direction.

Thanks
B

[HaHoBe]

Hi, boela,

maybe it should read

Please Login or Register  to view this content.

Ciao,
Holger

[boela]

Hi Holger.

Thanks for the reply. It worked!

Just for interest sake I changed the code to:

Range("A" & LastCell + 1 & ":A" & Rows.Count).ClearContents

But why do you add the second &" before the : separator?

I thought if you reference a range the syntax is for example Range("A1:A10")? And why is the first A in double quotation marks, and not single as in "A? The syntax VBA wants can sometimes be confusing

Just had a thought, is it because you have to convert the LastCell+1 number to a string, this is why the " after the first A and the " before the : separator applies to the LastCell+1 number? Maybe I jyst answered my own question

Thanks
B

[HaHoBe]

Hi, boela,

this is how VBA treats the inputs.

Please Login or Register  to view this content.

Although you have given a value to your Variable inside the " it is not considered as a variable but as part of the string. Knowing that a range-object for a cell should be given a column as well as a number for the row you may only combine it like

Please Login or Register  to view this content.

for a single cell or like stated above for a range (which could have been made up of cells-object as well).

Please keep in mind that just using range-objects will always refer to the active sheet. If you want to rely on other sheets orr even workbooks you must give name of sheets or workbooks to fully qualify the range.

Yeah, VBA seldom does what you want it to do and think you wrote the code for but always does what it should if you know a bit of the application model and the syntax to use.

Ciao,
Holger