Select and Print Only Cells with Values

[Adman]

Hi, newbie to VBA here. I have been researching and trying many ways to select and print a range, and I cannot find a way to print cells with values only.

In my case, I create a list of entries through the day. I am creating a button so I can print all of these entries at the end of the day.

These entries are sorted by column, and I will only be printing one column at a time.

The code that I have tried that isn't working is below. I realize that I am not using the "End" in its proper syntax. It was a futile effort after trying many other ways to code it in.

Sub Button1_Click()ActiveSheet.PageSetup.PrintArea = Range(A5, End(xlDown))
ActiveWindow.SelectedSheets.PrintOut ' print


End Sub

Any help in getting this up and running is much appreciated!

 

[ReggaeYT]

I'm not great with VBA myself, but have you tried switching "SelectedSheets" from line 2 into just "Selection"? First thing I noticed.

I've done it where I've had VBA select what I wanted, copy/paste it onto a new sheet, print, and then have the macro delete the spreadsheet in all one motion as well. I could help with that, though I'm sure the experts here have a more elegant solution

 

[Adman]

I'm not great with VBA myself, but have you tried switching "SelectedSheets" from line 2 into just "Selection"? First thing I noticed.

I've done it where I've had VBA select what I wanted, copy/paste it onto a new sheet, print, and then have the macro delete the spreadsheet in all one motion as well. I could help with that, though I'm sure the experts here have a more elegant solution

Thanks for your reply! I'm hoping to get a more concise line of code for this function so I can learn to work better within VBA.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Button1_Click()
ActiveSheet.PageSetup.PrintArea = Range("A5", Range("A5").End(xlDown)).Address
ActiveSheet.PrintOut ' print
End Sub

 

[Adman]

Thank you for the suggestion! That's exactly what I was looking for.

The code I have created that works for me:

Sub Butt*******1()ActiveSheet.PageSetup.PrintArea = Range("A5", Range("A5").End(xlDown)).Address
ActiveWindow.SelectedSheets.PrintOut ' print
End Sub

 

[Fluff]

You're welcome & thanks for the feedback