UsedRange on blank sheet?

[mike lee]

Hello,
Help didn't explain this very well, so I figured I'd ask
the experts. I'm writing a routine that will copy rows
containing exceptions on one sheet and paste them onto a
separate sheet so that they can be worked individually.
Here is that part of the code:

Set wksht = wkbk.Worksheets(2)
rownum = wksht.UsedRange.Rows.Count + 1

<more code>

wksht.Rows.EntireRow(rownum).PasteSpecial

The question is that, when wksht is blank, rownum starts
at 2 instead of one. Does UsedRange default to cell A1
if the sheet is blank or anything like that? If I have
data in wksht.row(1), then rownum is still 2. Am I on
the right track?

Thanks to all for the insight.

Mike

 

[steve]

Mike,

You guessed it.

put this into a module and see what you get:

MsgBox ActiveSheet.UsedRange.Rows.Address & vbCr _
& ActiveSheet.UsedRange.Rows.Count

The result should be the complete address of the used range and the second
line with the number of rows.

 

[Anders S]

Mike,

The used range of a new empty worksheet is cell A1.

One way to check this:

'****
Sub nextRowToUse()
Dim rowNum As Long
rowNum = ActiveSheet.UsedRange.Rows.Count + 1
If ActiveSheet.UsedRange.Cells.Count = 1 Then
rowNum = 1
End If
MsgBox rowNum
End Sub
'****

If A1 is not empty, the sub will still answer row 1, so you may have to check
for that as well.

HTH
Anders Silvén

 

[John Green]

The following function checks A1 for data when the used range is just A1:

Function NextRowToUse(ws As Worksheet) As Long
If ws.UsedRange.Address = "$A$1" And _
WorksheetFunction.CountA(ws.Range("A1")) = 0 Then
NextRowToUse = 1
Else
NextRowToUse = ws.UsedRange.Rows.Count + 1
End If
End Function

It does not address the situation where there are empty rows at the top of the worksheet. If this is possible, then you can use:

Function NextRowToUse(ws As Worksheet) As Long
If ws.UsedRange.Address = "$A$1" And _
WorksheetFunction.CountA(ws.Range("A1")) = 0 Then
NextRowToUse = 1
Else
With ws.UsedRange
NextRowToUse = .Rows(.Rows.Count).Row + 1
End With
End If
End Function