Type mismatch?

[StargateFan]

I found a macro that hopefully will do what I need, to select the rows
in an active sheet that have text in them. In other words, so that
blank rows aren't chosen, too.

*******************************************************************************
Sub SelectNonEmptyRows()
Dim Flag As Boolean
Dim R As Range
Dim Rng As Range

With ActiveSheet
Flag = False
For R = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
If Application.CountA(.Rows(R).Cells) <> 0 Then
If Flag = False Then
Set Rng = .Rows(R)
Else
Set Rng = Union(Rng, .Rows(R))
End If
Flag = True
End If
Next R

If Flag = False Then
MsgBox "Worksheet is Empty"
Else
Rng.Select
End If

End With
End Sub
*******************************************************************************
I found this here:
http://groups.google.ca/group/micro...+non-empty-rows&rnum=8&hl=en#dadf88255b886b57

OP said it worked for them but I get this error:

Compile error: Type mismatch

With the "R" in the line "For R" selected.

TIA for any help. D

 

[Dave Peterson]

R isn't a range. It's being used as a number:

Dim R As Long

 

[StargateFan]

R isn't a range. It's being used as a number:

Dim R As Long

Thanks. I made the change.

The macro now worked, but it didn't do the job. Not only does is
select all the rows in the entire print area even outside the area,
but it selects the empty ones, too! I don't know how this worked for
the original OP because they needed what I do judging from the
archived ng msg.

I give up. Does anyone know how to do this? I have 35 rows in this
print area but today's spreadsheet only has various cells fillled in
from rows 1 to 29. How could we get a macro to select the rows that
have text in them, in this case rows 1 to 29 in their entirety but
_only_ what falls within the print area? As an aside, the print area
extends over to column AI due to all the very small spacer columns.
Next time it might only be 20 rows that have some info in them,
another time might be 34 ...

Anyway, it feels like I'm close to completing this sheet. I can only
tell what needs to be done as I work with it <g>. Hope this can be
done, though.

Thanks! D



Sub SelectNonEmptyROWS()
ActiveSheet.Unprotect 'place at the beginning of the code
Dim Flag As Boolean
Dim R As Long
Dim Rng As Range

With ActiveSheet
Flag = False
For R = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
If Application.CountA(.Rows(R).Cells) <> 0 Then
If Flag = False Then
Set Rng = .Rows(R)
Else
Set Rng = Union(Rng, .Rows(R))
End If
Flag = True
End If
Next R

If Flag = False Then
MsgBox "Worksheet is Empty"
Else
Rng.Select
End If
End With

ActiveSheet.Protect ' place at end of code
End Sub

 

[Dave Peterson]

Maybe...

Option Explicit
Sub SelectNonEmptyRows()
Dim Flag As Boolean
Dim R As Long
Dim Rng As Range
Dim myRng As Range
Dim myAddr As String

With ActiveSheet
myAddr = ""
On Error Resume Next
myAddr = .PageSetup.PrintArea
On Error GoTo 0

If myAddr = "" Then
MsgBox "No print range set up"
Exit Sub
End If

Set myRng = .Range(myAddr)
Flag = False
For R = myRng.Row To myRng.Rows(myRng.Rows.Count).Row
If Application.CountA(.Rows(R).Cells) <> 0 Then
If Flag = False Then
Set Rng = .Rows(R)
Else
Set Rng = Union(Rng, .Rows(R))
End If
Flag = True
End If
Next R

If Flag = False Then
MsgBox "Worksheet is Empty"
Else
Rng.Select
End If
End With
End Sub

If you haven't set a print range, this code will stop.

If you want to use the .usedrange instead of stopping, you could change this
section:

If myAddr = "" Then
MsgBox "No print range set up"
Exit Sub
End If

to:
If myAddr = "" Then
myAddr = .UsedRange.Address
End If

======
Remember that if you have formulas that evaluate to "", that =counta() will
include them. And if you've converted those "" cells to values, excel will
still count them.

 

[StargateFan]

Maybe...

Option Explicit
Sub SelectNonEmptyRows()
Dim Flag As Boolean
Dim R As Long
Dim Rng As Range
Dim myRng As Range
Dim myAddr As String

With ActiveSheet
myAddr = ""
On Error Resume Next
myAddr = .PageSetup.PrintArea
On Error GoTo 0

If myAddr = "" Then
MsgBox "No print range set up"
Exit Sub
End If

Set myRng = .Range(myAddr)
Flag = False
For R = myRng.Row To myRng.Rows(myRng.Rows.Count).Row
If Application.CountA(.Rows(R).Cells) <> 0 Then
If Flag = False Then
Set Rng = .Rows(R)
Else
Set Rng = Union(Rng, .Rows(R))
End If
Flag = True
End If
Next R

If Flag = False Then
MsgBox "Worksheet is Empty"
Else
Rng.Select
End If
End With
End Sub

If you haven't set a print range, this code will stop.

No, has a print area. I always work that way -- unless you mean
something else with term "print range"?

Yeay. Though it's only a test, this did select only rows 1-29. But a
question, the entire rows look selected again. I tried it like that
anyway and pasted the resulting table into "Word and it didn't seem to
take junk from all the rest of the empty columns within the worksheet.
Will this be a problem down the road, though? It _seems_ to only take
what's in the print area but, again, what do I know? <g> Thought I'd
double-check.

Thanks. D

 

[Dave Peterson]

You only want the columns in the print range to be selected?

Option Explicit
Sub SelectNonEmptyRows()

Dim Flag As Boolean
Dim R As Long
Dim Rng As Range
Dim myRng As Range
Dim myAddr As String

With ActiveSheet
myAddr = ""
On Error Resume Next
myAddr = .PageSetup.PrintArea
On Error GoTo 0

If myAddr = "" Then
myAddr = .UsedRange.Address
End If

Set myRng = .Range(myAddr).Areas(1) 'only the first area
End With

Flag = False
With myRng
For R = 1 To .Rows.Count
If Application.CountA(.Rows(R).Cells) <> 0 Then
If Flag = False Then
Set Rng = .Rows(R)
Else
Set Rng = Union(Rng, .Rows(R))
End If
Flag = True
End If
Next R

If Flag = False Then
MsgBox "Worksheet is Empty"
Else
Rng.Select
End If

End With
End Sub