Solved Excel 2010 vba range using variable

Microsoft Excel 010 - complete package
January 6, 2012 at 12:02:21
Specs: Windows 7
VBA sub:

1. Identified the last row containing data and named it:
LastDataRow = ActiveSheet.UsedRange.Rows.Count
2. Subsequently want to AutoFill a column down to the last data row (and later do related things in other locations on the spreadsheet). Works fine as
Selection.AutoFill Destination:=ActiveCell.Range("A1:A181"), Type:= _
xlFillDefault (knowing that row 181 is the last one containing data)
3. However, would like to do this programmatically. Tried ...
.....Range("A1:LastDataRow")..... doesn't work, nor do a variety of other alternatives.

No doubt the syntax is wrong, but can't seem to find the correct way to express this.
Would appreciate any help. Thanks. Happy New Year

See More: Excel 2010 vba range using variable

January 6, 2012 at 22:01:26
✔ Best Answer
First, you should be aware that your use of UsedRange.Rows.Count will not always return the last row with data.

Open a new workbook and run this code:

Sub MyUsedRange()
   Range("B3") = "End of Data"
       MsgBox ActiveSheet.UsedRange.Rows.Count
   Range("A1") = "Start of Data"
       MsgBox ActiveSheet.UsedRange.Rows.Count
   Range("C10").Font.Bold = True
       MsgBox ActiveSheet.UsedRange.Rows.Count
End Sub

With data is B3 only, UsedRange.Rows.Count will return 1. Therefore, using LastDataRow = UsedRange.Rows.Count will not accurately reflect the last row with data, which is actually 3.

With data in A1 and B3, LastDataRow will return 3 which is correct since Row 1 contains data and therefore all rows are counted. Unfortunately we can't always be sure that Row 1 will contain data.

Finally, since the UsedRange includes cells that have been formatted, formatting C10 will increase the UsedRange.Rows.Count well beyond the last row with data.

The more common way to determine the last row with data is as follows, assuming you know that Column A is the longest (or equal to the longest) column with data:

LastDataRow = Range("A" & Rows.Count).End(xlUp).Row

If you don't know which Column is the longest, then you can loop through the used columns and determine which one is the longest.

Sub FindLastRowWithdata()
 For colNum = 1 To ActiveSheet.UsedRange.Columns.Count
    If Cells(Rows.Count, colNum).End(xlUp).Row > LastDataRow Then
        LastDataRow = Cells(Rows.Count, colNum).End(xlUp).Row
    End If
    MsgBox LastDataRow
End Sub

As far as the syntax you are looking for, I believe that you should be using something like this:

Range("A1:A" & LastDataRow)

Since a hardcoded range would look like Range("A1:A10"), you have to give VBA everything it wants when you build that range with a variable.

"A1:A" concatenated with a variable gives VBA the full range address, assuming that the variable contains a valid row number, e.g. 10.

"A1:A" & LastDataRow = "A1:A" & 10 = "A1:A10"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 7, 2012 at 11:21:08
This got at both aspects of my post: Identifying the last row allows me to save it as a variable, while the syntax for using a variable in a range lets me use it elsewhere. Both work perfectly. Thank you.

Searching on Google re: finding the last row returns a number of different approaches, some better than others and a few with the flaw you mentioned in the UsedRange.Rows.Count method. Lesson: Be careful - not only of what you ask for, but what is offered in reply. ;-)

Report •

January 9, 2012 at 11:43:55
Well, just to perseverate on this issue, what about using a variable in an Offset statement? I assume Offset wants an integer, but, for example,

Dim countSum as integer

results in Error 1004
or (if Dim as string)
ActiveCell.Offset(countSum & ",0").Range("A1").Select
results in Error 13

Report •

Related Solutions

January 9, 2012 at 12:19:43
I don't get any errors as long as I set countSum equal to an integer, e.g.

countSum = 2

However, I get strange results if countSum is Dimmed as a String...but I can't imagine why you would do that.

With countSum Dimmed as a string and set equal to 2, mousing over countSum & ",0" shows that it evaluates to "2,0" which VBA is picking up as 20 and using that as the Row Offset.

Using Offset(countSum & ",0", 3) results in an Offset of 20 Rows, 3 Columns.

But again, I can't think of any reason why you would use that syntax.

In addition, what is the purpose of Range("A1") in your code?

Finally, you are aware that you rarely have to actually Select a cell to perform an VBA operation on it, aren't you?

Selecting cells and/or ranges just slows your code down.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Ask Question