Search range for empty columns

[smithcza]

Hi

I want to search a range of data, if the entire column within the range is empty, I want to hide the column. So far I am looping through the range with to loops. This is probably not the most efficient way. Can sombody please adcise me on this?

My code:

Sub Test2()


Dim sStartRange
Dim lCol, lRow

sStartRange = "E22"

Range(sStartRange).Select
For lCol = 1 To 5
For lRow = 1 To 5
If ActiveCell.Value = "" Then
Selection.Offset(1, 0).Select
Else
Selection.Offset(1, 0).Select
iCount = iCount + 1
End If
Next
If iCount = 0 Then
Selection.EntireColumn.Hidden = True
End If
Selection.Offset(-lRow, 1).Select
iCount = 0
Next

End Sub


Regards,

SmithcZA

 

[PHV]

A starting point:
Sub Test2()
Dim lCol As Long, lRow As Long, bFlag As Boolean
For lCol = 5 To 9
bFlag = True
For lRow = 22 To 26
If Trim(ActiveSheet.Cells(lRow, 5)) <> "" Then
bFlag = False
Exit For
End If
ActiveSheet.Columns(lCol).EntireColumn.Hidden = bFlag
Next
Next
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CBasicAsslember]

Using the CountBlank function:

Sub Test2()

Dim sStartRange
Dim iCol As Integer
Dim iRow As Integer
Dim iColStart As Integer

iRow = 22
iColStart = 5

For iCol = iColStart To iColStart + 4

    If Application.CountBlank(Sheet1.Range(Cells(iRow, iCol), Cells(iRow + 4, iCol))) = 5 Then
        Sheet1.Columns(iCol).EntireColumn.Hidden = True
    Else
        Sheet1.Columns(iCol).EntireColumn.Hidden = False
    End If
Next
 
End Sub

 

[SkipVought]

SmithcZA,

Your scenario raises a red flag. I realize that sometimes you get workbooks over which you have no control, but table designs with empty columns or rows are a bad design and ought to be avoided.

Be as it may, here's yet another approch...

    r1 = Cells.Rows.Count
    For i = 1 To 5
        With Cells(1, i)
            If r1 = .End(xlDown).Row Then
                .EntireColumn.Hidden = True
            Else
                .EntireColumn.Hidden = False
            End If
        End With
    Next

Skip,

_[red][/red]

 

[smithcza]

Hi thanks guys.

Will give it all a go.

SkipVought, the problem is I populate from an external DB, which will only has data for every monday of a month. This data sits in columns (one column per day), hence only four out of a possable 31 columns will be populated. The client doesn't like the empty columns, therefore I would like to hide them. I can't delete them either because the workbook is live, it will only know "all mondays" by end of the month. Every month has a new workbook, which makes displaying only data for "Mondays" rather tricky.

Regards,

SmithcZA

 

[SkipVought]

I'd take the imported data a NORMALIZE. One colum per day is rediculous and make it VERY DIFFICULT to analyze and report.

Skip,

_[red][/red]

 

[CBasicAsslember]

Instead of hiding the columns, could you hide the 31 column worksheet and copy the data to a different worksheet? The worksheet function WEEKDAY may also be of some use identifying which days are Monday for any given month.

 

[smithcza]

Thanks Skip

I think we have a bit of a misunderstanding here. The database represents the data in a more strucktured way like you would expect from a MSSQL database. The client wanted to see the data in a "Montly" report, tests performed on a daily basis. With the tests in rows and days in columns. So I need the columns etc. in the final Excel report.

Thanks for all the advise guys, there are obviously many ways to skin this cat. I like the idea of the weekdays function, this way I can trim the report to size on the beginning of the month and no need for the hiding colums routine everytime the report updates.

Regards,

SmithcZA

 

[SkipVought]

No misunderstanding at all on my end.

You STORE data in a properly designed database.

Then you REPORT the data in the way that your customer want to see it, using the plethora of tools that Excel has, that are designed to work in properly designed sturctures.

That's how it works, plain and simple!

Skip,

_[red][/red]