Create a print macro that would automatically select print area?

How do I create a macro to automatically select the print area that has
contents in certain cells?

Hi wastedwings

Can you give more information


--
Regards Ron de Bruin
http://www.rondebruin.nl


"wastedwings" <wastedwings@discussions.microsoft.com> wrote in message news:53BCEC4F-1E05-46D6-B5A3-25D208913B1F@microsoft.com...
> How do I create a macro to automatically select the print area that has
> contents in certain cells?

I have a worksheet that contains information that updates from another
worksheet (contains links). I print the worksheet out each time it is
updated, but I would like to create a macro that would automatically select
the print area of the cells that contain information, instead of going in and
selecting the print area. Since there are cells that do not have information,
but contain links to another worksheet for future linking, hitting the print
button will not print the cells that contain information only, but will only
print a portion of the worksheet.

"Ron de Bruin" wrote:

> Hi wastedwings
>
> Can you give more information
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "wastedwings" <wastedwings@discussions.microsoft.com> wrote in message news:53BCEC4F-1E05-46D6-B5A3-25D208913B1F@microsoft.com...
> > How do I create a macro to automatically select the print area that has
> > contents in certain cells?
>
>
>

>> but contain links to another worksheet for future linking

Maybe this is a easy solution for you

If you have zero's in the cells then you can use
Tools>Options...View
Uncheck Zero values

--
Regards Ron de Bruin
http://www.rondebruin.nl


"wastedwings" <wastedwings@discussions.microsoft.com> wrote in message news:FF086612-26A1-4AB4-8D7A-510E64E65A44@microsoft.com...
>I have a worksheet that contains information that updates from another
> worksheet (contains links). I print the worksheet out each time it is
> updated, but I would like to create a macro that would automatically select
> the print area of the cells that contain information, instead of going in and
> selecting the print area. Since there are cells that do not have information,
> but contain links to another worksheet for future linking, hitting the print
> button will not print the cells that contain information only, but will only
> print a portion of the worksheet.
>
> "Ron de Bruin" wrote:
>
>> Hi wastedwings
>>
>> Can you give more information
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "wastedwings" <wastedwings@discussions.microsoft.com> wrote in message news:53BCEC4F-1E05-46D6-B5A3-25D208913B1F@microsoft.com...
>> > How do I create a macro to automatically select the print area that has
>> > contents in certain cells?
>>
>>
>>

Ok, I tried that. Now when I check the print preview, it prints like 17 pages
of empty cells. How can I create a macro to Print using "print if cells
contain...."?

"Ron de Bruin" wrote:

> >> but contain links to another worksheet for future linking
>
> Maybe this is a easy solution for you
>
> If you have zero's in the cells then you can use
> Tools>Options...View
> Uncheck Zero values
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "wastedwings" <wastedwings@discussions.microsoft.com> wrote in message news:FF086612-26A1-4AB4-8D7A-510E64E65A44@microsoft.com...
> >I have a worksheet that contains information that updates from another
> > worksheet (contains links). I print the worksheet out each time it is
> > updated, but I would like to create a macro that would automatically select
> > the print area of the cells that contain information, instead of going in and
> > selecting the print area. Since there are cells that do not have information,
> > but contain links to another worksheet for future linking, hitting the print
> > button will not print the cells that contain information only, but will only
> > print a portion of the worksheet.
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi wastedwings
> >>
> >> Can you give more information
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >> "wastedwings" <wastedwings@discussions.microsoft.com> wrote in message news:53BCEC4F-1E05-46D6-B5A3-25D208913B1F@microsoft.com...
> >> > How do I create a macro to automatically select the print area that has
> >> > contents in certain cells?
> >>
> >>
> >>
>
>
>

We can make a filter / print macro
Do you have headers above your data and if you do in which row
Can we check one column in your data table if it contains data <> then 0 (which column)

I will make a example for you if you give me the information


--
Regards Ron de Bruin
http://www.rondebruin.nl


"wastedwings" <wastedwings@discussions.microsoft.com> wrote in message news:8FC447D7-A024-4403-B49E-C1AA226D2125@microsoft.com...
> Ok, I tried that. Now when I check the print preview, it prints like 17 pages
> of empty cells. How can I create a macro to Print using "print if cells
> contain...."?
>
> "Ron de Bruin" wrote:
>
>> >> but contain links to another worksheet for future linking
>>
>> Maybe this is a easy solution for you
>>
>> If you have zero's in the cells then you can use
>> Tools>Options...View
>> Uncheck Zero values
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "wastedwings" <wastedwings@discussions.microsoft.com> wrote in message news:FF086612-26A1-4AB4-8D7A-510E64E65A44@microsoft.com...
>> >I have a worksheet that contains information that updates from another
>> > worksheet (contains links). I print the worksheet out each time it is
>> > updated, but I would like to create a macro that would automatically select
>> > the print area of the cells that contain information, instead of going in and
>> > selecting the print area. Since there are cells that do not have information,
>> > but contain links to another worksheet for future linking, hitting the print
>> > button will not print the cells that contain information only, but will only
>> > print a portion of the worksheet.
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Hi wastedwings
>> >>
>> >> Can you give more information
>> >>
>> >>
>> >> --
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >>
>> >> "wastedwings" <wastedwings@discussions.microsoft.com> wrote in message
>> >> news:53BCEC4F-1E05-46D6-B5A3-25D208913B1F@microsoft.com...
>> >> > How do I create a macro to automatically select the print area that has
>> >> > contents in certain cells?
>> >>
>> >>
>> >>
>>
>>
>>

The data consists of columns A through E with the header row being on Row 1

"Ron de Bruin" wrote:

> We can make a filter / print macro
> Do you have headers above your data and if you do in which row
> Can we check one column in your data table if it contains data <> then 0 (which column)
>
> I will make a example for you if you give me the information
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "wastedwings" <wastedwings@discussions.microsoft.com> wrote in message news:8FC447D7-A024-4403-B49E-C1AA226D2125@microsoft.com...
> > Ok, I tried that. Now when I check the print preview, it prints like 17 pages
> > of empty cells. How can I create a macro to Print using "print if cells
> > contain...."?
> >
> > "Ron de Bruin" wrote:
> >
> >> >> but contain links to another worksheet for future linking
> >>
> >> Maybe this is a easy solution for you
> >>
> >> If you have zero's in the cells then you can use
> >> Tools>Options...View
> >> Uncheck Zero values
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >> "wastedwings" <wastedwings@discussions.microsoft.com> wrote in message news:FF086612-26A1-4AB4-8D7A-510E64E65A44@microsoft.com...
> >> >I have a worksheet that contains information that updates from another
> >> > worksheet (contains links). I print the worksheet out each time it is
> >> > updated, but I would like to create a macro that would automatically select
> >> > the print area of the cells that contain information, instead of going in and
> >> > selecting the print area. Since there are cells that do not have information,
> >> > but contain links to another worksheet for future linking, hitting the print
> >> > button will not print the cells that contain information only, but will only
> >> > print a portion of the worksheet.
> >> >
> >> > "Ron de Bruin" wrote:
> >> >
> >> >> Hi wastedwings
> >> >>
> >> >> Can you give more information
> >> >>
> >> >>
> >> >> --
> >> >> Regards Ron de Bruin
> >> >> http://www.rondebruin.nl
> >> >>
> >> >>
> >> >> "wastedwings" <wastedwings@discussions.microsoft.com> wrote in message
> >> >> news:53BCEC4F-1E05-46D6-B5A3-25D208913B1F@microsoft.com...
> >> >> > How do I create a macro to automatically select the print area that has
> >> >> > contents in certain cells?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

OK

Try this for a sheet named "Sheet1"

Note: I filter on the A column for non zeros so i assume that you don't have zeros in your
data in column A.(only zeros below your data)

Delete preview:=True if it is working correct

Sub Test_With_AutoFilter()
Dim WS As Worksheet
Dim rng As Range
Dim Str As String

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change
Str = "<>0" '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str
WS.PrintOut preview:=True
WS.AutoFilterMode = False
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"wastedwings" <wastedwings@discussions.microsoft.com> wrote in message news:F2286575-E9CE-484B-A0AB-BAAD9B336B15@microsoft.com...
> The data consists of columns A through E with the header row being on Row 1
>
> "Ron de Bruin" wrote:
>
>> We can make a filter / print macro
>> Do you have headers above your data and if you do in which row
>> Can we check one column in your data table if it contains data <> then 0 (which column)
>>
>> I will make a example for you if you give me the information
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "wastedwings" <wastedwings@discussions.microsoft.com> wrote in message news:8FC447D7-A024-4403-B49E-C1AA226D2125@microsoft.com...
>> > Ok, I tried that. Now when I check the print preview, it prints like 17 pages
>> > of empty cells. How can I create a macro to Print using "print if cells
>> > contain...."?
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> >> but contain links to another worksheet for future linking
>> >>
>> >> Maybe this is a easy solution for you
>> >>
>> >> If you have zero's in the cells then you can use
>> >> Tools>Options...View
>> >> Uncheck Zero values
>> >>
>> >> --
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >>
>> >> "wastedwings" <wastedwings@discussions.microsoft.com> wrote in message
>> >> news:FF086612-26A1-4AB4-8D7A-510E64E65A44@microsoft.com...
>> >> >I have a worksheet that contains information that updates from another
>> >> > worksheet (contains links). I print the worksheet out each time it is
>> >> > updated, but I would like to create a macro that would automatically select
>> >> > the print area of the cells that contain information, instead of going in and
>> >> > selecting the print area. Since there are cells that do not have information,
>> >> > but contain links to another worksheet for future linking, hitting the print
>> >> > button will not print the cells that contain information only, but will only
>> >> > print a portion of the worksheet.
>> >> >
>> >> > "Ron de Bruin" wrote:
>> >> >
>> >> >> Hi wastedwings
>> >> >>
>> >> >> Can you give more information
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Regards Ron de Bruin
>> >> >> http://www.rondebruin.nl
>> >> >>
>> >> >>
>> >> >> "wastedwings" <wastedwings@discussions.microsoft.com> wrote in message
>> >> >> news:53BCEC4F-1E05-46D6-B5A3-25D208913B1F@microsoft.com...
>> >> >> > How do I create a macro to automatically select the print area that has
>> >> >> > contents in certain cells?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>