UsedRange on blank sheet?

  • Thread starter Thread starter mike lee
  • Start date Start date
M

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
 
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.
 
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
 
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
 
Back
Top