EmptySheet.UsedRange.Rows.Count = 1 ?

[markask]

Hi,

Do you know why
Debug.Print Sheets(1).UsedRange.Rows.Count is 1
for an empty sheet??

 

[PHV]

Perhaps a sheet can't be empty ?

 

[markask]

It courses a mistake:

For i = 1 to 5
'copy something
Sheets(1).Range("A" & Sheets(1).UsedRange.Rows.Count+1).Paste
Next

 

[SkipVought]

With YourSheet.UsedRange
  If .Cells(1, 1).Value <> "" Or .Cells.Count > 1 Then
     'you have something to print
  End If
End With

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[markask]

So, all of you believe that
Sheets(1).UsedRange.Rows.Count = 1 for an empty sheet is not a bug......

 

[rmikesmith]

marask,

It's a bug in this version of Excel but will be upgraded to a feature in the next!

Usedrange can be a troublesome beast. Excel often gets confused about what the actual UsedRange is. I've seen many forum threads on getting the real UsedRange. I use it as little as possible. Give Skip's workaround a try.


Mike

 

[markask]

Many thaks to rmikesmith and all

 

[xlbo]

The problem is that rows is an integer that cannot be 0.

Rows(0) causes an error - therefore, excel cannot have 0 rows used, therefore, minimum usedrange.rows.count = 1. It's logical (to me)

Rgds, Geoff

Well, he's kind of had it in for DPlank ever since he accidentally ran over his dog. Actually, replace "accidentally" with "repeatedly," and replace "dog" with "son."

Please read FAQ222-2244 before you ask a question

 

[Zathras]

The used range on an "empty" sheet can be seen by running this macro:

Sub Test()
  MsgBox ActiveSheet.UsedRange.Address
End Sub