Print only certain rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Malcolm Hughes - 25 Feb 2006 17:23 GMT

Help please.

I have a listing, 000's of rows, ~ 20 columns and I only want to print those
that match certain criteria and I am looking for a VBA solution.
Details are:
I have indicated the rows to print by inserting a txt character [can be
changed to value] in each row, in a spare column.
I know my problem can be solved with filtering but that method is not
appropriate here.
I am using Excel 2003 and 2000.

I have tried and failed to write a macro to toggle [toggle = desirable, not
essential] hiding the rows fitting my criteria and have failed. I have also
searched the web, found some code that comes close but still failed to
produce anything that works. My VBA skills are sadly lacking.

Many thanks to anyone who can help.

Malcolm Hughes

Reply to this Message

Paul B - 26 Feb 2006 01:31 GMT

"I know my problem can be solved with filtering but that method is not
appropriate here"
Why is it not appropriate here?
How about a macro to cut the filters on, filter your data, print the sheet
and cut the filters back off, something like this

Sub HideRows()
Dim mytext As String
mytext = InputBox("Enter Your criteria", "Match Criteria")
Range("A1").Select
Selection.AutoFilter

'set for column 21, U
Selection.AutoFilter Field:=21, Criteria1:=mytext

'use to test
ActiveWindow.SelectedSheets.PrintPreview

'use to print
'ActiveSheet.PrintOut Copies:=1, Collate:=False

Selection.AutoFilter
End Sub

Signature

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

> Help please.
>
[quoted text clipped - 15 lines]
>
> Malcolm Hughes

Reply to this Message

MPH - 26 Feb 2006 11:36 GMT

Thanks for that. I'll try it.
My unsuccessful method was GoTo Col [say C], select cells matching [say N],
hide.

> "I know my problem can be solved with filtering but that method is not
> appropriate here"
[quoted text clipped - 42 lines]
>>
>> Malcolm Hughes

Reply to this Message

MPH - 27 Feb 2006 20:25 GMT

Thanks again for your help. I tried your solution and made it work - as I
had said, my VB skills are limited.

Solution I have gone for is:
To use an error value to identify the rows I don't want to print, select
cells, hide rows, then the reverse to unhide.
Sub HideError()
ActiveWindow.Panes(1).Activate
'change column to valid column, if selection below is not as req'd
Columns("D:D").Select
Selection.SpecialCells(xlCellTypeFormulas, 16).Select
Selection.EntireRow.Hidden = True
End Sub
Sub UnhideError()
Columns("D:D").Select
Range("D5").Activate
Selection.SpecialCells(xlCellTypeFormulas, 16).Select
Selection.EntireRow.Hidden = False
End Sub

Now I just need to find out how to create toolbar [for the above] that saves
only in current file.
Thanks again for your help.

> "I know my problem can be solved with filtering but that method is not
> appropriate here"
[quoted text clipped - 42 lines]
>>
>> Malcolm Hughes

Reply to this Message

Paul B - 27 Feb 2006 22:53 GMT

MPH, have a look here for a way to do it'
http://groups.google.co.uk/group/microsoft.public.excel/browse_frm/thread/93cb24
da834596ef/e16d67b1cc4a1f65

Signature

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

> Thanks again for your help. I tried your solution and made it work - as I
> had said, my VB skills are limited.
[quoted text clipped - 65 lines]
> >>
> >> Malcolm Hughes

Reply to this Message

Paul B - 27 Feb 2006 23:01 GMT

MPH, you don't have to select the cells you can use this

Columns("D:D").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Hidden = True

Signature

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

> Thanks again for your help. I tried your solution and made it work - as I
> had said, my VB skills are limited.
[quoted text clipped - 65 lines]
> >>
> >> Malcolm Hughes

Reply to this Message

Rate this thread: