Referring to Variable Ranges in VBA for Excel

[paulo33]

I am trying to write some code that will count the number of total rows in a particuar sheet, and then delete the data in them, but only for some of the columns.

The counting bit was easy just setting a variable equal to:
UsedRange.Rows.Count

However, when I try to reference a Range wichi includes this variable, it doesn't like it. Here's the code I've written:

TotalRows = Sheets("Costs").UsedRange.Rows.Count
Sheets("Costs").Select
Range(Cells(3,11),Cells(TotalRows,13)).Select
Selection.ClearContents

As you see, the idea is quite simple, clear the contents, from Cells(3,11) to Cells(TotalRows,13), but this doesn't seem to work.
Help please...

 

[RobBroekhuis]

I see no reason your code shouldn't work. Note you can more efficiently code three of your lines as:

Sheets("Costs&quot.Range(Cells(3,11),Cells(TotalRows,13)).ClearContents

to avoid using the selection object (hardly ever used by most VBA programmers).

If it still doesn't work, let us know the error message you get, and the value of the totalrows variable.
Rob

 

[paulo33]

RobBroekhuis, thanks
The code is now working. I wasn't using the value returned by UsedRange.Rows.Count, but I was using a value from a loop I did, that just went through all the rows, until it found an empty one, assiging a 'counter' to it.

Although, like I said, my code is now working, why doesn't it work using the loop method?

Thanks

 

[RobBroekhuis]

Paulo,
Post your code for us to look at - it's probably just a little glitsch in syntax or logic.
Rob

 

[paulo33]

Again, thanks for all the help!!
However, now its not working again in another part of the code. I'm sending it all over, hope you can help.

Sub CopyData()
[COLOR=green]'Copies data from "Data" sheet, into "ConversionCosts" sheet[/color]

Dim ConversionRows As Integer
Dim DataRows As Integer

ConversionRows = Sheets("ConversionCosts").UsedRange.Rows.Count + 2
DataRows = Sheets("Data").UsedRange.Rows.Count + 2

Sheets("ConversionCosts").Range(Cells(2, 1), Cells(ConversionRows, 10)).ClearContents
[COLOR=green]'Clears data in ConversionCosts sheet[/color]
Range(Cells(3, 11), Cells(ConversionRows, 13)).ClearContents
[COLOR=green]'Clears formulas, leaving first row as source for next operation[/color]

Sheets("Data").Range(Cells(2, 1), Cells(DataRows, 10)).Copy
   [COLOR=red]'Copies data from Data sheet to ConversionCosts sheet. This line is where I am getting an error[/color]
    
Sheets("ConversionCosts").Select
Range("K2:M2").Select [COLOR=green]'Copies formulas from 2nd row to rest of rows[/color]
    Selection.Copy
    Sheets("ConversionCosts").Range(Cells(3, 11), Cells(DataRows, 13)).Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

End Sub

 

[paulo33]

Sorry, the tags didn't seem to work????
I'm sending the code again, to make it easier to read.

Sub CopyData()
'Copies data from "Data" sheet, into "ConversionCosts" sheet

Dim ConversionRows As Integer
Dim DataRows As Integer

ConversionRows = Sheets("ConversionCosts").UsedRange.Rows.Count + 2
DataRows = Sheets("Data").UsedRange.Rows.Count + 2

Sheets("ConversionCosts").Range(Cells(2, 1), Cells(ConversionRows, 10)).ClearContents
'Clears data in ConversionCosts sheet
Range(Cells(3, 11), Cells(ConversionRows, 13)).ClearContents
'Clears formulas, leaving first row as source for next operation

Sheets("Data").Range(Cells(2, 1), Cells(DataRows, 10)).Copy
   'Copies data from Data sheet to ConversionCosts sheet. This line is where I am having an error message
    
Sheets("ConversionCosts").Select
Range("K2:M2").Select 'Copies formulas from 2nd row to rest of rows
    Selection.Copy
    Sheets("ConversionCosts").Range(Cells(3, 11), Cells(DataRows, 13)).Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

End Sub

The error message that I am getting is " '1004':
Application defined, or object defined error"

 

[RobBroekhuis]

I'm not sure why the copy statement gives you an error message (does sheets("data&quot exist? is the value of DataRows reasonable?) but I do know that this statement will not copy anything to the ConversionCosts sheet - it only copies the range from the Data sheet to the clipboard. You can add a parameter to the copy statement to designate a direct copy to another range, for example:

Sheets("Data&quot.Range(Cells(2, 1), Cells(DataRows, 10)).Copy _
sheets("ConversionCosts&quot.cells(2,1)

Rob