Macro to hide empty columns in a range is not working

[glennpc]

I have a range of data with many columns, and each day it will be updated-- it will always have the same columns but a variable number of rows. The goal is that my macro will look at a specific set of 39 columns in the middle of the range beginning with the right most one, and examine the cells (not including a header name cell) in that column to see if they are null. If they are all null, the macro is supposed to hide the column and then move a column to the left and repeat. The macro should stop when it finds a column that has data in one of the cells. This is the code:

Sub HideEmptyColumns()

Application.ScreenUpdating = False

Dim lRow As Long
lRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row

a_tab = ActiveSheet.Name
For x = 49 To 11 Step -1
anybody = False
For y = 8 To lRow
chk_yx = Worksheets(a_tab).Cells(y, x)
If Not IsNull(chk_yx) Then anybody = True
Next y
If Not anybody Then Worksheets(a_tab).Cells(1, x).ColumnWidth = 0
Next x

Application.ScreenUpdating = True

End Sub

This looks like it ought to work, but when I test it, it does not hide the columns that it should. Anybody see an error?

 

[mumps]

Try:

Sub FindAddress()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    Dim x As Long
    For x = lCol 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

 

[TestKing]

An alternative approach: Try this will hide all blank columns (excluding 1st row) in the region with the active cell. You can change the loops start/end values if you need. Will be faster.

Sub hidecols()
'************************************************************************************************
'*
'************************************************************************************************
Dim cNbr, rNbr, maxRows, maxCols As Integer
Dim cr As Range
Set cr = ActiveSheet.Cells.CurrentRegion
maxRows = UBound(cr.Value, 1) '* Rows
maxCols = UBound(cr.Value, 2) '* Columns
For cNbr = 1 To maxCols
For rNbr = 2 To maxRows
If Not isNothing(ActiveSheet.Cells(rNbr, cNbr)) Then Exit For
Next rNbr
If rNbr = maxRows + 1 Then Columns(cNbr).entireColumn.Hidden = True
Next cNbr
End Sub
Public Function isNothing(ByVal theString As String) As Boolean
'************************************************************************************************
'* Check if there is anything in a string (to avoid testing for isnull, isempty, and
'* zero-length strings)
'* isNothing(" This is my string ") returns False
'************************************************************************************************

isNothing = theString & "" = ""

End Function

 

[glennpc]

Using column 49 as the starting point didn't work either.

 

[mumps]

@glennpc: Whose code are you referring to in your last post? The macro I suggested in Post# 2 should work regardless of which column is the last used column.

 

[glennpc]

I was referring to Mumps code. I wrote you a reply to your suggested code, but it never got through. I changed the name of the macro, and I also got an error with a reference to "Sheet1", so I fixed that. But your code looks for the last column on the sheet, and the columns that I need to check are columns 49 to 11 (working backwards.). I have columns to the right of that. I thought it was finding them and not deleting anything, but I changed lCol to 49 as the starting point and it still didn't hide the columns I expected (I had all blanks in Columns 49 and 48, so those were expected to be hidden, and they didn't get hidden).

 

[mumps]

I think that it would be much easier to follow if I could see how your data is organized. Perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[glennpc]

I just sent a quick reply-- again did not go through. My first one said that I tried the dropbox and I realized that I can't do it from work. I then described my range of data. The key thing is that the macro should only run against columns 11 through 49. I have columns to the right of that and they should not be tested. The number of rows varies, so using the last row variable is good. But whenever I run the code, it doesn't delete any columns. Does it look at formatting? I have boxes around every cell in the range.

 

[mumps]

Please refer to Post# 7.