How to Print Based on Row Height

[JARHTMD]

I'm using Excel2007. With the exception of my headers, only column AC (sometimes) wraps to multiple lines based on input data. Several columns are protected. Rows 1 thru 3 and cols A & B are frozen to always be visible.

How can I print only certain cells & only in rows with increased row height?

If Cell AC has wrapped (increased height); I want to print colA, colB, colAC. Do this only for each row whose height is greater than my "standard" row height.

 

[Yongle]

How about

Sub HideSomeRowsAndColumns()
    Dim cel As Range
    With ActiveSheet
        For Each cel In .Range("A4", .Range("A" & Rows.Count).End(xlUp))
            If cel.RowHeight <= 15 Then cel.EntireRow.Hidden = True
        Next cel
        .Range("C:AB").EntireColumn.Hidden = True
        .Range("AD:AZ").EntireColumn.Hidden = True
    End With
End Sub

Sub UnHideThings()
    With ActiveSheet.UsedRange
        .EntireRow.Hidden = False
        .EntireColumn.Hidden = False
     End With
End Sub

Note
Needs amending if you already have hidden rows and columns that should be maintained

 

[JARHTMD]

Thanks, Yongle, for your reply.

I saved my spreadsheet to TEST.xlsm (macro enabled). In TEST I added your coding and ran HideSomeRowsAndColumns. Its results were exactly what I want. I did not run the Unhide... macro.

I thought I'd try tweaking it a bit (just feeling my way w/o knowing what I was doing). I figured I could always come back to your coding. Your note about any already hidden rows and columns reminded me that rows 4 thru 16, which contain data from previous year, are hidden (out-of-sight, out-of-mind). So I decided to try modifying the Hide... macro to start in A17, instead of A4, just to see if that would work. Also the last row containing data in col D would signal the end of user-entered data & the last possible row to show, but I don't know how to make that test & didn't even attempt.

Anyway, I got a run-time error 1004 "unable to set the hidden property of Range class". I tried several more times; starting from scratch each time . . . save my original spreadsheet; add your code (unchanged); run; error 1004. Nothing is making sense to me now. I even tried running the Unhide... macro w/o 1st running Hide... & got the same error.

 

[Yongle]

In that case try...

Sub HideSomeRowsAndColumns()
    Dim cel As Range
    With ActiveSheet
        For Each cel In .Range("D17", .Range("D" & Rows.Count).End(xlUp))
            If cel.RowHeight <= 15 Then cel.EntireRow.Hidden = True
        Next cel
        .Range("C:AB").EntireColumn.Hidden = True
        .Range("AD:AZ").EntireColumn.Hidden = True
    End With
End Sub

Sub UnHideThings()
    With ActiveSheet.UsedRange
        .EntireRow.Hidden = False
        .EntireColumn.Hidden = False
        .Rows("4:16").Hidden = True
     End With
End Sub

Explanation of changes

macro to start in A17, instead of A4....
Also the last row containing data in col D would signal the end of user-entered data & the last possible row to show, but I don't know how to make that test & didn't even attempt.

see changes in this line

  For Each cel In .Range("[COLOR=#ff0000]D17[/COLOR]", .Range("[COLOR=#ff0000]D[/COLOR]" & Rows.Count).End(xlUp))

reminded me that rows 4 thru 16, which contain data from previous year, are hidden

Unhide all rows and then hide the ones that need to remain hidden

        .EntireRow.Hidden = False
        ......
        .Rows("[COLOR=#ff0000]4:16[/COLOR]").Hidden = True

 

[JARHTMD]

Thanks for being patient with me, but I just can't get it to work. I think my problem might be that I don't know how to add the coding. I've always either recorded macros or pasted coding such as yours among already existing macros for a spreadsheet. So in this case, I recorded a "dummy" macro that does nothing but jump to another cell.

Then . . .
View Macros/select the "dummy" macro/Edit
Pasted your coding (copied directly from your post)
"X" out
This has always(?) worked for me. In fact, it worked on my 1st try at adding your original code.

To run . . .
View Macros/HideSomeRowsAndColumns/Run
Error 1004

What am I doing wrong?

 

[Yongle]

run-time error 1004 "unable to set the hidden property of Range class"

Can be caused by a number of things none of which are obvious...

Is the worksheet protected in any way?
- it is not possible to hide columns\rows when sheet protection box allowing user to format columns\rows is left unchecked

Are there any drawn objects on the worksheet?
- it is not possible to hide a row\column if the properties of an object positioned there do not permit it to resize

Are you using cell "comments"?
- in some earlier versions of Excel, rows and columns cannot be hidden if a cell to be hidden contains a comment
- it makes no logical sense and is not the case with more recent versions!

 

[JARHTMD]

I created a macro to copy my current (2018) sheet to end of workbook & unprotect the new sheet. More on that later. Then I run HideSomeRowsAndColumns on the new sheet. It works, but does not stop after the last entry in column D. It appears to run all the way to the max number of rows possible in Excel (65,000+?). That's not a major problem, because the final results are correct. I can tell because it takes much longer than expected to run (for < 400 rows) and by the formatting of columns A&B in the empty rows at the bottom of the output. Col AC is not formatted.

My sheet has formatted columns, including borders and (color) fill. This was done by selecting columns & formatting. That, of course, extends far beyond the number of rows that I use. Actually, my sheet is for 1 year only. Each January I copy & setup for the new year, including 2 weeks of the old year . . . that's why rows 4-16 are hidden.

I tried removing the border format for column D to see if that counts as used, but that didn't shorten the run time or change the output. Actually, the run time isn't critical. I can live with that. This procedure will be used relatively infrequently.

My new (recorded) macro . . .
Sub CopyAndUnprotect()
'
' Copy worksheet (to end) and unprotect
'
Sheets("2018").Select
Sheets("2018").Copy After:=Sheets(16)
Sheets("2018 (2)").Select
ActiveSheet.Unprotect
'
Application.Run "HideSomeRowsAndColumns"
End Sub

How do I replace "2018" with [current sheet name] & Sheets(16) with [end of workbook] so I don't have to modify in the future?

Thanks, again, for your help.