Try this code:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim TotalRows As Long
Dim TotalCols As Long

Set wks = Worksheets.Add

With wks
.Range("x21:z30").Value = "x"

TotalRows = .UsedRange.Rows.Count
TotalCols = .UsedRange.Columns.Count
MsgBox "Test#1:" & vbLf & _
"Column: " & .UsedRange.Columns.Count & vbLf & _
"row: " & .UsedRange.Columns.Count & vbLf & _
"Address: " & .Range("A1", .Cells(TotalRows, TotalCols)).Address

With .UsedRange
TotalRows = .Rows(.Rows.Count).Row
TotalCols = .Columns(.Columns.Count).Column
End With
MsgBox "Test#2:" & vbLf & _
"Column: " & .UsedRange.Columns.Count & vbLf & _
"row: " & .UsedRange.Columns.Count & vbLf & _
"Address: " & .Range("A1", .Cells(TotalRows, TotalCols)).Address
End With

End Sub

It creates a new sheet and populates a range with some x's. But A1 doesn't get
populated.

Excel is smart enough to keep track of the usedrange--if you use A1, then clear
it, excel still thinks that the .usedrange starts in A1. But it may not always
be true in all cases.

Sometimes the .usedrange doesn't start in row 1 or column A.


ingineu wrote:

Sorry, forgot to ask ...
And if your data doesn't start in A1. your totalrows and totalcols
variables
may not be what you expect. So I changed it.
I'm not sure what the difference is between the 2 methods. When you
say "data doesn't start in A1", are you referring to blank lines?

--
ingineu
------------------------------------------------------------------------
ingineu's Profile: http://www.excelforum.com/member.php...o&userid=14860
View this thread: http://www.excelforum.com/showthread...hreadid=513944

--

Dave Peterson