Anyone good at excel macro's?

[marra]

Hi all, I need to add a button to an excel spreadsheet that will hide all rows that have a blank cell in column A1. I'm pretty sure this is achievable as I've managed to do it to hide blank rows but I only want them hidden if only column A1 is blank. Hope this makes sense. Can anyone help?

 

[Meeko]

The following should do what you require.

Sub Button1_Click()
     Application.ScreenUpdating = False
     Application.Calculation = xlCalculationManual
     Range("A1:A1000").Select
     For Each Cell In Selection
          If Cell.Value = "" Then
               Cell.EntireRow.Hidden = True
          End If
     Next Cell
     Application.Calculation = xlCalculationAutomatic
     Application.ScreenUpdating = True
End Sub

You'll need to change the "A1:A1000" part if depending on the number of rows in your spreadsheet.

 

[RockLobster]

Damn you Meeko lol. Mine is pretty much the same although I check how many rows there are used.

Sub Test()
  Dim currentWorkSheet As Worksheet
  Set currentWorkSheet = ActiveSheet

  Dim usedRowsCount
  usedRowsCount = currentWorkSheet.UsedRange.Rows.Count

  For Row = 1 To usedRowsCount
     'Check if column is empty, if empty hide the row
     If currentWorkSheet.Cells(Row, 1).Value = "" Then
        Cells(Row, 1).EntireRow.Hidden = True
     End If
  Next
End Sub

 

[Meeko]

Damn you Meeko lol. Mine is pretty much the same although I check how many rows there are used.

Ah, clever! I've never thought about using the UsedRowsCount before! Will need to remember it for the future. Cheers RockLobster!

And hopefully, we've both given Marra the solution he is looking for!

 

[marra]

Thats great guys both work perfectly for what I need. Only one other thing is it possible to create a button to undo it?

 

[Talbs13]

.Visible = True