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