Print selected rows

mega · December 14th, 2004, 10:41 AM

Hi.
I need to select the rows in my sheet without background color and I do that, with this code snippet, but I can't figure out how to apply my new Range to a Select. Anyone?

Code:

  Dim strRange As String
  Range("A1").CurrentRegion.Select
  With Selection
    For Each r In .Rows
      If r.Interior.ColorIndex = xlColorIndexNone Then
        strRange = strRange & returnRangeForRow(r.Address) & ", "
      End If
    Next r
  End With
  strRange = Right(strRange, (Len(strRange) - 1))

Public Function returnRangeForRow(ByVal strRange As String)
  strRange = Right(strRange, (Len(strRange) - InStrRev(strRange, "$"))) '"$A$38"
  strRange = strRange & ":" & strRange  '"38"
  returnRangeForRow = strRange  '"38:38"
End Function

I tried a million things that I won't list here but I can say it's not as easy as typing: Range("1" & strRange).Select
Selection.Offset(0, 1).Select
Selection.PrintOut Copies:=1, Preview:=True, Collate:=True

- mega
Moving to C# .NET

BrianB · December 15th, 2004, 11:26 AM

This is a method. However, if you want to print the rows Excel will put each one on a separate page. Better to hide the unwanted ones.

Code:

Sub test()
    Dim PrintRange As Range
    '-----------------------
    If Selection.Cells.Count = 1 Then Exit Sub
    With Selection
        For Each r In .Rows
            If r.Interior.ColorIndex = _
                    xlColorIndexNone Then
                If PrintRange Is Nothing Then
                    Set PrintRange = r
                Else
                    Set PrintRange = _
                        Union(PrintRange, r)
                End If
            End If
        Next r
    End With
    PrintRange.Select
    PrintRange.PrintPreview
End Sub

-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.