Autofit target row

[ABabeNChrist]

I'm trying to use Worksheet_Change to autofit only target row selection in column A rows 8:200
This is what I current have and can the row height be +10

Code: Select all

    Dim rng As Range
    For Each rng In Target.Rows
        Select Case rng.Row
            Case 1, 2, 3, 4, 5, 6, 7 ' Rows that will be skipped
                ' Do nothing
            Case Else
                With rng
                    .EntireRow.AutoFit
                End With
        End Select
    Next rng

[HansV]

So: is there a problem?
Do you get an error message? If so, what does it say?
Do you get a different result than you wanted/expected? If so, in what way?

[ABabeNChrist]

No error, it just affects other columns and beyond row 200

[HansV]

Try this:

Code: Select all

    Dim rng As Range
    For Each rng In Target.Rows
        Select Case rng.Row
            Case 8 To 200
                rng.EntireRow.AutoFit
        End Select
    Next rng

This will still affect other columns though. See my next reply.

[HansV]

Or, without looping, hence shorter and faster:

Code: Select all

    Dim rng As Range
    Set rng = Intersect(Range("A8:A200"), Target)
    If Not rng Is Nothing Then
        rng.EntireRow.AutoFit
    End If

[HansV]

can the row height be +10

What do you mean by that?

[ABabeNChrist]

The + 10 was in reference adding additional height of 10.

[YasserKhalil]

May be after the line of Mr. HansV code (rng.EntireRow.Autofit)
Put this line

Code: Select all

rng.RowHeight = rng.RowHeight + 10

[HansV]

Since row heights can vary from row to row, we'll have to loop again:

Code: Select all

    Dim rng As Range
    If Not Intersect(Range("A8:A200"), Target) Is Nothing Then
        For Each rng In Intersect(Range("A8:A200"), Target).Rows
            rng.EntireRow.AutoFit
            rng.RowHeight = rng.RowHeight + 10
        Next rng
    End If