Macro to print variable number of rows

Hi,

You can use the following code to select/define the area to be printed

Please Login or Register  to view this content.

Then you just apply it to the printing part of your code manuelly or record it.

Steffen Thomsen

Originally Posted by Steffen Thomsen

Hi,

You can use the following code to select/define the area to be printed

Please Login or Register  to view this content.

Then you just apply it to the printing part of your code manuelly or record it.

Steffen Thomsen

Hi Steffen

Thanks so much for your help.

I have put in the code as you suggested, as follows:

Please Login or Register  to view this content.

The issue I am now having is that when I run the macro, it goes to the 'print preview' page, and the only cells that the print preview shows me are
- the cell which is currently selected (i.e. where my cursor last clicked)
- the two cells relating to the column headers (rows 8 and 9)

So if I have C13 selected, print preview shows C8, C9, C13 as the part of the worksheet that will be printed.

Ideally, I would want to incorporate the macro as a button on the spreadsheet, so that a user can just click 'Print Worksheet' and (instead of coming up with the print preview screen) the worksheet would be sent to the printer...

Many thanks again for your improvement to my code!

DP

Try using this instead of the print line

Please Login or Register  to view this content.

Steffen Thomsen

I've tried it with that code but unfortunately nothing goes to the printer. I also tried closing excel, but still nothing goes to the printer. And as a final (random?) idea, I put

Please Login or Register  to view this content.

But that only opened up the debugger...

Hi again,

I made a little mistake, this should work.

Please Login or Register  to view this content.

Make sure you have set your printer to default.

Steffen Thomsen

That did work... but here's the catch -

Rows 1-16 have text in them, but rows 17-1008 don't. However, there are a set of columns in rows 11-1008 that have been formatted with data validation, so that when users are entering data into the spreadsheet, it is formatted for their use (e.g. with dropdowns).

So, currently using the above code prints the whole worksheet, rows 1-1008, because most of the rows (even if they are blank) have the data validation.

Is there a way to select only the rows which have [one or more] cells filled in? so, rows where there is one [or a set of] values in columns A-L.

Apologies for making this ever more convoluted...!

Hi again,

Do you have a single row that only filled to the size og the printarea?

Or maby you can upload a dummyworkbook?

Steffen Thomsen

Hello,

I've attached a dummy worksheet now which has the basic layout.

Thanks very much!

DP

Hi,

I've been working on some further code, and I came up with this:

Please Login or Register  to view this content.

But there I get a 'compile' error on the line beginning 'LastRow'...

I think perhaps it would be a better idea, instead of automatically sending all the rows to 'print', the macro instead goes to the Print Preview screen, so users can see what they will be printing...

I'd greatly appreciate a bit of help in tweaking with this code because all I seem to be doing with this is just copy+pasting from various other (related and unrelated!) codes I find online to amalgamate into what I need a macro to do...

Many thanks

DP

Just to say, I have adapted some code now for the above problem

Please Login or Register  to view this content.

This bring up the print preview screen with rows of data that have info in them.

Thanks a lot for your help

DP