Hide columns that have no data (except column header)

[glennpc]

I had earlier code on this but could not get it to work. This is new code that is supposed to work, but still has a glitch somewhere. What the HideLabelColumns macro is supposed to do is look at a part of my range (only columns I through AW) and it should examine them from right to left-- if the column range (for each of the columns, the data are rows 8 through 14 although this will vary every day when the macro is run), if they are all blank, it should hide the column, then move to the left one column and do the same thing, continuing until it hits a column that has data in it-- when it finds that, it exits the sub. I tried to attach my workbook but it is too large. I have data in at least one cell in all the columns except AW and AV, so the macro should hide those columns only and leave all the rest. But when I run it, it hides nothing. The code looks like this:

Sub HideLabelColumns()
  Application.ScreenUpdating = False
  Dim LastRow As Long
  LastRow = Sheets("general_report").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  Dim lCol As Long
  lCol = ActiveSheet.UsedRange.Columns.Count
  Dim x As Long
  For x = 49 To 1 Step -1
  If WorksheetFunction.CountA(Range(Cells(2, x), Cells(LastRow, x))) = 0 Then
  Columns(x).EntireColumn.Hidden = True
  Exit Sub
  End If
  Next x
  Application.ScreenUpdating = True
End Sub

Can anyone tell me what I have wrong in this sub? Would you absolutely need a copy of my workbook to look at? I might be able to remove a lot of other macros and send you that version if it makes it small enough.

 

[Luke M]

Give this a shot:

Sub HideLabelColumns()
Dim LastRow As Long
Dim lCol As Long
Dim x As Long

Application.ScreenUpdating = False

LastRow = Sheets("general_report").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

lCol = ActiveSheet.UsedRange.Columns.Count

For x = 49 To 1 Step -1
    If WorksheetFunction.CountA(Range(Cells(2, x), Cells(LastRow, x))) = 0 Then
        Columns(x).EntireColumn.Hidden = True
    Else
        'Exit if we DO find data
        Exit Sub
    End If
Next x
Application.ScreenUpdating = True
End Sub

If still not working, verify that the columns are truly blank, and not having some sort of "" mark, or other character. You can check by just putting a ISBLANK formula in the workbook to test the cells.

 

[glennpc]

Luke-- I ran your code on my spreadsheet, and it did not work. It should have hidden columns AW and AV and they are still there. I tried the ISBLANK formula for each of the cells =ISBLANK(AW8) through =ISBLANK(AW14) as you suggested and they all came out TRUE. One thing that is quirky about my worksheet is that I have data in columns A and B that extend down past row 14-- but the range I'm looking at in this macro should only include Columns C through AW and rows 8 through 14. I'm not sure what this line is looking at exactly:

If WorksheetFunction.CountA(Range(Cells(2, x), Cells(LastRow, x))) = 0

 

[glennpc]

Luke: UPDATE! I got your code to work, with a couple of tweaks. First, I used a different method to get the LastRow, which keyed off column C (the first row the macro should care about, since columns A and B are different), and I changed the Reference to Range(Cells(2,x) to Range(Cells(8,x), since my table is not at the top of the spreadsheet, but the header row is row 7 and the first row of data is row 8. I stepped through it and the looping worked fine, and it hid the columns I needed to be hidden. I'll test it some more, but it looks like it works! Thanks!