deleting rows in specific range if blank

[Macklin]

I have this written and it works to check one column for "", and then hides the row. I want to check the range (E:K) of each row and if they are all blank or "" more specifically, then hide that row. Also I would like to know how to select more than one group of rows for the "i" if it is possible. Eg. (1-18) and (23-30). Thank you for any help.

Sub Rectangle4_Click()
Dim i As Integer
  Application.ScreenUpdating = False
For i = 1 To 18
    If Sheets("calendar").Cells(i, "E").Value = "" Then
    Rows(i).Hidden = True
    End If
Next i
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

This is how I would write the code...

Sub HideEmptyRows()
  Dim R As Range
  Const Cols As String = "E:K"
  Const Rws As String = "1:18,23:30"
  For Each R In Intersect(Columns(Cols), Range(Rws)).Rows
    R.Hidden = R.SpecialCells(xlCellTypeBlanks).Count = Columns("E:K").Columns.Count
  Next
End Sub

You can define the ranges is the two constant (the Const) statements as a comma separated list of either row or columns ranges as shown.

Note: I went with your description and worked with the hidden property as opposed to doing what your subject line asked for.

 

[Macklin]

Yeah, I meant hide. Just made an error, you chose right. Thank you for your help, I am going to give this a shot. It is much appreciated.

 

[Rick Rothstein]

I should mention... the code will take care of both hiding and unhiding the rows in response to any changes you make in the cells within the specified range. So, if in code, you place some values in hidden cells within Columns E:K, and clear all the value in that column range is visible cells, and then run the macro, all the rows will become properly hidden or unhidden as the column range dictates. Given that, perhaps I could have chosen a better macro name, maybe, HideEmptyRowsUnhideNonEmptyRows.

 

[Macklin]

I get an error box code 1004 saying "no cells found" with this line highlighted

    R.Hidden = R.SpecialCells(xlCellTypeBlanks).Count = Columns("E:K").Columns.Count

I didn't do anything to change it, just pasted it directly in, is their anything that needs defined or anything?

 

[Rick Rothstein]

Sorry, I missed the worksheet reference to the 'calendar' sheet in your originally posted code... my code assumed the sheet with the rows to be hidden was the ActiveSheet which apparently is not the case. Easily fixed. Use this code instead...

Sub HideEmptyRows()
  Dim R As Range
  Const Cols As String = "E:K"
  Const Rws As String = "1:18,23:30"
  Const ShtName As String = "calendar"
  For Each R In Intersect(Worksheets(ShtName).Columns(Cols), Worksheets(ShtName).Range(Rws)).Rows
    R.Hidden = R.SpecialCells(xlCellTypeBlanks).Count = Columns("E:K").Columns.Count
  Next
End Sub

 

[Macklin]

I did run it in the active sheet and now also tried it with the specific sheet name and still no luck. I took some time and found one spot that I think needed changed with the ")" symbol at the end, but that didn't seem to help.
I changed that on this line from-

  For Each R In Intersect(Worksheets(ShtName).Columns(Cols), Worksheets(ShtName).Range(Rws)).Rows

to this-

For Each R In Intersect(Worksheets(ShtName).Columns(Cols), Worksheets(ShtName).Range(Rws).Rows)

I also tried running it in a blank excel sheet with the same error. I can't seem to figure this one out. Thank you for your time. I will keep at it, if you see anything else I appreciate it. Being new to excel vba I am concerned that it might just be me, did you get it to run on a test sheet?

 

[Rick Rothstein]

Being new to excel vba I am concerned that it might just be me, did you get it to run on a test sheet?

Yes, I pretty much test all my code before posting it. If you want, you can send my a copy of your workbook so I can examine it directly and see why my code is not running correctly on it. If you want to do that, my email address is rickDOTnewsATverizonDOTnet (just replace the upper case words with the symbols they spell out).

 

[Rick Rothstein]

Okay, I looked at the file you sent me. First off, I had left off an On Error Resume Next statement. Second, I made the assumption that since you wanted to check Columns E:K, that those columns would have bee populated; howver, the file you sent me only had data out to Column H. The SpecialCells function only returns information for cells within the UsedRange, so my column count comparison was off because of this. Also, some of your "empty" cells had space characters in them. Anyway, assuming you do a Replace operation to remove and space characters in cells you intend to be blank, here is code which should work around all the other problems I came across...

Sub HideEmptyRows()
  Dim R As Range, ColRng As Range
  Const Cols As String = "E:K"
  Const Rws As String = "1:18,23:30"
  Const ShtName As String = "calendar"
  Set ColRng = Intersect(Worksheets(ShtName).UsedRange.Columns, Worksheets(ShtName).Columns("E:K")).Columns
  On Error Resume Next
  For Each R In Intersect(ColRng, Worksheets(ShtName).Range(Rws)).Rows
    R.Hidden = R.SpecialCells(xlCellTypeBlanks).Count = ColRng.Columns.Count
  Next
End Sub