Want row to stay hidden

I run the following macro to fax a ride list:

Sub FaxIt()
Dim CurPrinter As String
Range("Names").AutoFilter Field:=1, Criteria1:="<>"
CurPrinter = Application.ActivePrinter
ActiveWorkbook.PrintOut , ActivePrinter:="Brother PC-FAX on BMFC:"
Range("Names").AutoFilter
Application.ActivePrinter = CurPrinter
End Sub

There are times when I want to exclude a rider or two temporarily by hiding
its row prior to faxing. I do this manually with Format | Row | Hide, but
the macro unhides it.

How can I make my chosen row(s) stay hidden?

--
David

It appears that your Autofilter is causing the hidden lines to be unhidden before faxing.

A possible workaound would be to have the code check which rows are hidden before the autofilter is applied and then re-hide those rows just before sending the fax.

bhofsetz wrote

> It appears that your Autofilter is causing the hidden lines to be
> unhidden before faxing.

True

> A possible workaound would be to have the code check which rows are
> hidden before the autofilter is applied and then re-hide those rows
> just before sending the fax.

That's what I'm seeking because I don't know how to code that.

--
David

Hi David,

Perhaps something like this would work for you:

Sub Tester()
Dim Rng As Range, Rng2 As Range
Dim rCell As Range
Dim sh As Worksheet

Set sh = Sheets("Sheet1") ' <<===== Change

If Not sh.AutoFilterMode Then
Range("A1").AutoFilter ' <<===== Change?
End If

Set Rng2 = sh.AutoFilter.Range.Columns(1)

For Each rCell In Rng2.Cells
If rCell.EntireRow.Hidden Then
If Not Rng Is Nothing Then
Set Rng = Union(Rng, rCell)
Else
Set Rng = rCell
End If
End If
Next

Application.ScreenUpdating = False

'your filter code here

Rng.EntireRow.Hidden = True
Application.ScreenUpdating = True

End Sub
---
Regards,
Norman



"David" <dturner4_1999@yahoo.com> wrote in message
news:OKWvdQEeFHA.2700@tk2msftngp13.phx.gbl...
> bhofsetz wrote
>
>> It appears that your Autofilter is causing the hidden lines to be
>> unhidden before faxing.
>
> True
>
>> A possible workaound would be to have the code check which rows are
>> hidden before the autofilter is applied and then re-hide those rows
>> just before sending the fax.
>
> That's what I'm seeking because I don't know how to code that.
>
> --
> David

Norman Jones wrote

> Hi David,
>
> Perhaps something like this would work for you:
>
> Sub Tester()
> Dim Rng As Range, Rng2 As Range
> Dim rCell As Range
> Dim sh As Worksheet
>
> Set sh = Sheets("Sheet1") ' <<===== Change
>
> If Not sh.AutoFilterMode Then
> Range("A1").AutoFilter ' <<===== Change?
> End If
>
> Set Rng2 = sh.AutoFilter.Range.Columns(1)
>
> For Each rCell In Rng2.Cells
> If rCell.EntireRow.Hidden Then
> If Not Rng Is Nothing Then
> Set Rng = Union(Rng, rCell)
> Else
> Set Rng = rCell
> End If
> End If
> Next
>
> Application.ScreenUpdating = False
>
> 'your filter code here
>
> Rng.EntireRow.Hidden = True
> Application.ScreenUpdating = True
>
> End Sub
> ---
> Regards,
> Norman

Thanks Norman
I may decide to adopt that method on a future project, but for now I've
opted out of using AutoFilter altogether. Instead:

Sub FaxIt()
Dim CurPrinter As String
Range("Names").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
CurPrinter = Application.ActivePrinter
ActiveWorkbook.PrintOut , ActivePrinter:="Brother PC-FAX on BMFC:"
Application.ActivePrinter = CurPrinter
Range("Names").EntireRow.Hidden = False
End Sub

This works as desired and has the added bonus that makes it unnecessary
for me to remember to unhide the rows I hid manually before faxing.

--
David