Excel Print Area Adjust

[rjm65]

This is my first time ever using a macro, so I'm not sure of what's possible or not. I have an Excel spreadsheet with 4 columns, Column A is quantity, Column B is item code, Column C is item description, and Column D is item cost. I have placed a button on my sheet that uses the code found below. It runs autofilter so that it only displays those items with an entry in column A.

Is there someway to have it adjust the print area automatically so that only the results of the autofilter print? I see a reference in the code to print area, but I have no idea where to start.

Thanks in advance,
Raymond

Private Sub CommandButton1_Click()
'
' Order_Only Macro
' Macro recorded 10/5/2004 by Raymond'
' Keyboard Shortcut: Ctrl+Shift+O
'
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>"
ActiveSheet.PageSetup.PrintArea = ""
ActiveWindow.SmallScroll Down:=-6
End Sub

 

[SkipVought]

Hi,

How could it print anything BUT what has been filtered.

It could not possibly be printing the rows that ARE hidden.

I do not understand the problem.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[PHV]

Is there someway to have it adjust the print area automatically so that only the results of the autofilter print
Yes, doing nothing more, as this is the standard behaviour of Excel.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

You did not state that there were other columns in addition to the ones described. Is THAT the case?

Private Sub CommandButton1_Click()
'
' Order_Only Macro
' Macro recorded 10/5/2004 by Raymond'
' Keyboard Shortcut: Ctrl+Shift+O
'
    with Range("A1")
      .AutoFilter
      .AutoFilter Field:=1, Criteria1:="<>"
      ActiveSheet.PageSetup.PrintArea = .CurrentRegion
    end with
End Sub

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[rjm65]

Skip,
Only the 4 columns described above have information, roughly 1000 rows worth of entries (our entire product line). The "commandbutton" is in column F (which I prefer not print out).

I cleared the print area but that doesn't seem to help.

 

[PHV]

Have you tried this ?
ActiveSheet.PageSetup.PrintArea = "$A:$D"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rjm65]

ActiveSheet.PageSetup.PrintArea = "$A:$D"

That worked, but only after I started with a NEW spreadsheet. It appears that previously it was printing all cells that had any type of formatting in them.

Thanks!

 

[SkipVought]

I prefer

ActiveSheet.PageSetup.PrintArea = [A1].CurrentRegion

assuming that you have a value in A1

AND

all your print data is contiguous.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[rjm65]

Skip,

I receive a Run-time error '1004': Application-defined or object-defined error when I try the above. And yes to value in A1 and print data is contiguous.

Raymond

 

[SkipVought]

sorry, it wants an address string and not a range object

ActiveSheet.PageSetup.PrintArea = [A1].CurrentRegion.Address

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[rjm65]

Thanks Skip, this works perfectly. The only problem I have now is that I would like to protect the sheet to preserve formatting and data information i.e, part number, pricing etc. I have checked the "allow users to use autofilter" and "allow users to select unlocked cells" in the sheet protect properties, but when I click on the button that links to my autofilter macro I get the following message "Run Time Error 1004, You cannot use this command on an protected worksheet, to unprotect......"

Is there anyway around this?
Thanks,
Raymond