Highlighting a Block of Cells to Define the Print Area

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

It used to be easy in the "old" days to capture a macro in which you
anchor the upper left most cell of the target range and then hold
shift and press the right arrow, then the down arrow to highlight a
range of cells.

You could then make this the print area, change formatting, etc.

I haven't learned the syntax for such in Excel's VB. It would be
insightful to see how to write this in a macro (module) and also in
the sheet where it could be executed with an active-x conrol.

Thanks in advance for any examples, suggestions, etc.

Mark
 
Here is one way of doing it.

Private Sub CommandButton1_Click()

intRow = 1: intCol = 1

Do While Cells(1, intCol) <> ""
intCol = intCol + 1
Loop

Do While Cells(intRow, 1) <> ""
intRow = intRow + 1
Loop

If intCol > 1 Then
Range("a1:" & Chr(intCol + 63) & intRow - 1).Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$" _
& Chr(intCol + 63) & "$" & intRow - 1
Range("A1").Select
End If

End Sub


The code starts in cell A1. If you want to start in a different cell you
will have to use ActiveCell.Address as the starting point.
 
set rng = Activesheets.Range("A1").CurrentRegion
Activesheet.PageSetup.PrintArea = rng.Address(external:=True)
 
hi
In the exemple behind, A1 is the most upper left cell of the range you want. This should work if you have the same type of data in all your range.... You can't have a blank column for exemple, or an error column...But else, it should be working.

Range("A1:" & Range(Range("A1").End(xlDown).Address).End(xlToRight).Address).select
 
or another which does specifically what you describe.

set rng = Range(Range("B9"),Range("B9").End(xlDown).End(xlToRight))
ActiveSheet.PageSetup.PrintArea = rng.Address(external:=True)
 
Ran the code below once and it changed the region to bold.
I manually changed it back to regular.
Then ran the code a second time and nothing happened, i.e. it didn't
bold the text.

Private Sub CommandButton1_Click()
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim xlRng As Object

Set xlApp = Application
Set xlWb = ActiveWorkbook
Set xlWs = xlWb.Worksheets("sheet1")

Set xlRng = ActiveSheet.Range("a1").CurrentRegion
Selection.Font.Bold = True

Set xlRng = Nothing
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
End Sub

Why didn't it work the second time?

Thanks, Mark

Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas
 
you spend a lot of time declaring and seeting your variables, then work with
Activesheet and selection. Your code does nothing with either the selection
or activesheet, so it is a crap shoot at that point as to what will be
changed.

Private Sub CommandButton1_Click()
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim xlRng As Object

Set xlApp = Application
Set xlWb = xlApp.ActiveWorkbook
Set xlWs = xlWb.Worksheets("sheet1")

Set xlRng = xlWs.Range("a1").CurrentRegion
xlRng.Font.Bold = True

Set xlRng = Nothing
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
End Sub
 
Guess I'm missing a fundamental base, Tom. Was trying to execute one of
your suggestions (but rather than set the print range I was trying to
control the bold font). Sounds like I made it more difficult than it
could be.

Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas
 
For anyone interested... here's the complete solution. This code is in
Sheet1 and is associated with an Active-X control button
(CommandButton1). This code will toggle the font from bold to regular.

Private Sub CommandButton1_Click()
Set xlRng = ActiveCell.CurrentRegion
If xlRng.Font.Bold = True Then
xlRng.Font.Bold = False
Else
xlRng.Font.Bold = True
End If
End Sub

Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas
 
Back
Top