Using VBA to Autofit Visible Rows

[bbradford]

Hello VBA Express,

I am very new to VBA and struggling to come up with a way to have a spreadsheet automatically hide rows where column G is "No" and display rows with the value "Yes". I have been successful in getting the first part to work, but when I update the data so that a previously "No" value is now "Yes", the row remains invisible until manually resized. I am using Excel 2013 without any addons. Here is the code that I am running:



Private Sub Worksheet_Calculate()
Dim lastrow As Long, c As Range
Application.EnableEvents = False
lastrow = Cells(Cells.Rows.Count, "G").End(xlUp).Row
On Error Resume Next

For Each c In Range("G1:G" & lastrow)
If c.Value = "No" Then
c.EntireRow.Hidden = True
ElseIf c.Value = "Yes" Then
c.EntireRow.Hidden = False
c.EntireRow.AutoFit
End If
Next

On Error GoTo 0
Application.EnableEvents = True
End Sub


Thanks in advance for any help you are able to offer.

[vcoolio]

Hello Bbradford,

It should work as a Worksheet_Change event rather than a Worksheet_Calculate event

You could also change this line:-

ElseIf c.Value = "Yes" Then

to:-


Else:

Cheerio,
vcoolio.