VBA to change row height to last row with data

[Shadkng]

I would like some help to modify the code below to adjust row height and font size only down to the last row with data. Currently it adjusts the rows for the entire sheet which doesn't seem necessary. This code also hides both rows and columns based on various criteria. Is the code efficient the way it's written? Thanks

Sub CLUTCH_BUILD_HIDE_ALL()
Dim xRg As Long
Application.ScreenUpdating = False
With Sheets("CLUTCH BUILD")
ActiveSheet.UsedRange.Font.Size = 16
ActiveSheet.UsedRange.RowHeight = 30
Cells.EntireColumn.AutoFit
For i = 1 To ActiveSheet.UsedRange.Columns.Count
Columns(i).ColumnWidth = Columns(i).ColumnWidth + 3
Next i
.Rows("11:20").Hidden = False
For xRg = 11 To 20
.Rows(xRg).Hidden = .Cells(xRg, 2) = "" Or .Cells(xRg, 2) = "-"
Next xRg
End With
Dim c As Range
For Each c In Range("A24:N24").Cells
If c.Value = "HIDE" Then
c.EntireColumn.Hidden = True
End If
Next c
Application.ScreenUpdating = True
End Sub

 

[Norie]

Try this.

Sub CLUTCH_BUILD_HIDE_ALL()
Dim c As Range
Dim xRg As Long

    Application.ScreenUpdating = False

    With Sheets("CLUTCH BUILD")
        .UsedRange.Font.Size = 16
        .UsedRange.RowHeight = 30
        .Cells.EntireColumn.AutoFit
        
        For i = 1 To .UsedRange.Columns.Count
            .Columns(i).ColumnWidth = .Columns(i).ColumnWidth + 3
        Next i
        
        .Rows("11:20").Hidden = False
        
        For xRg = 11 To 20
            .Rows(xRg).Hidden = .Cells(xRg, 2) = "" Or .Cells(xRg, 2) = "-"
        Next xRg

        For Each c In .Range("A24:N24").Cells
            If c.Value = "HIDE" Then
                c.EntireColumn.Hidden = True
            End If
        Next c

    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[Shadkng]

It works but doesn't seem to do anything differently. What about changing row height and font size for just the rows that have data? Is it necessary to do that?

 

[Norie]

What do you mean by 'works'?

Is it doing what you want it to?

 

[Shadkng]

Yes it is and I see you cleaned up the code a bit.

My other request was to have the row height and font only change for rows that have data. Now it changes them for the entire sheet. Thanks

 

[Norie]

This should change the height and font of only the range with data.

    With Sheets("CLUTCH BUILD")
        .UsedRange.Font.Size = 16
        .UsedRange.RowHeight = 30

Mind you, UsedRange can sometimes be unreliable especially if you've done a lot of formatting/copying/pasting etc.

See if this is any better.

Sub CLUTCH_BUILD_HIDE_ALL()
Dim c As Range
Dim xRg As Long
Dim I As Long

    Application.ScreenUpdating = False

    With Sheets("CLUTCH BUILD")
        .Range("A1").CurrentRegion.Font.Size = 16
        .Range("A1").CurrentRegion.RowHeight = 30
        .Cells.EntireColumn.AutoFit
        
        For i = 1 To .Range("A1").CurrentRegion.Columns.Count
            .Columns(i).ColumnWidth = .Columns(i).ColumnWidth + 3
        Next i
        
        .Rows("11:20").Hidden = False
        
        For xRg = 11 To 20
            .Rows(xRg).Hidden = .Cells(xRg, 2) = "" Or .Cells(xRg, 2) = "-"
        Next xRg

        For Each c In .Range("A24:N24").Cells
            If c.Value = "HIDE" Then
                c.EntireColumn.Hidden = True
            End If
        Next c

    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[Shadkng]

That didn't work either. Actually the rows and fonts didn't change at all. Isn't there a code to simulate cont/shift down arrow which hi-lights rows down until a blank row?

 

[Norie]

Where does the data on 'CLUTCH BUILD' start?

 

[Shadkng]

It starts on row 1, but starting on row 2 would be better.