Excel Macro to read Strings and Values in Rows 1

[asumter]

I am currently developing an Excel macro in VB that can format spreadsheets that I receive everyday. I have developed a code that reads down Column A which contains numeric values(0-10) in order to pinpoint the exact rows that I need formatted. For example, the code will read down Column A, find any cell with a 2, and make that row bold. The code has no problem reading up and down ColumnA, however, I have run into a problem when trying to read cells across the row. I want the code to recognize any cell in Column A with a numeric value of 5, read across the row(through cells containing text, nulls, and numbers), and recognize all cells containing a 0. After the cell is determined to have a 0 in it, I want the code to hide the entire column and move on until the end of the range. This is what I have so far.



Dim CurrentCell As Object, NextCell As Object

ActiveWorkbook.Sheets("Sheet1&quot.Select
Set CurrentCell = ActiveSheet.Range("A1&quot
Do While Not IsEmpty(CurrentCell)
Set NextCell = CurrentCell.Offset(1, 0)
'reads down Column A to pinpoint values
If CurrentCell.Value = 5 Then
Do While ActiveSheet.Range("NoNull&quot.Select
Set NextCell = CurrentCell.Offset(0, 1)
'attempts to read across row
If CurrentCell = 0 Then
Columns.Select
Selection.EntireColumn.Hidden = True
End If
Set CurrentCell = NextCell
Loop
End If
Set CurrentCell = NextCell
Loop

End Sub


The code will not step through the rows and I believe that it is getting stuck on the cells containing text, null, and numbers that are not 0. So, in essence, I am trying to figure out a way to get my code to recognize whether an active cell is a string, a null, or a numeric value. Any advice would be greatly appreciated.

 

[joerd]

I am not exactly sure what you need, but the following code may get you going into the right direction. I got a bit confused with your CurrentCell and NextCell, so I changed it to have CurrentRow point to the first cell of the row you are examining, and have CurrentCol go through all the cells in the row until it finds an empty cell (then CurrentRow is increased). The sub stops when CurrentRow finds an empty cell. You may want to modify the sub to stop on other conditions, it's up to you.
To prevent the sub failing when it reads a string instead of a number, I have added an If statement testing for IsNumeric(CurrentCol) - this may after all be the only thing you were looking for... ;-)

Sub whatever()
Dim CurrentRow As Range, CurrentCol As Range
    
    ActiveWorkbook.Sheets("Sheet1").Select
    Set CurrentRow = ActiveSheet.Range("A1")
    Do While Not IsEmpty(CurrentRow)
    'reads down Column A to pinpoint values
        If IsNumeric(CurrentRow) Then
            If CurrentRow.Value = 5 Then
                Set CurrentCol = CurrentRow.Offset(0, 1)
                Do While Not IsEmpty(CurrentCol)
        'attempts to read across row
                    If IsNumeric(CurrentCol) Then
                        If CurrentCol.Value = 0 Then CurrentCol.Columns.Hidden = True
                    End If
                    Set CurrentCol = CurrentCol.Offset(0, 1)
                Loop
            End If
        End If
        Set CurrentRow = CurrentRow.Offset(1, 0)
    Loop
End Sub

Cheers,

Joerd

 

[asumter]

Joerd,

Thank you very much for your help. I was unaware that the IsNumeric function could read through null values as well as bypass the strings. I have inputted your code into my spreadsheet and it works like a charm. I give you credit for helping me with my problem. Again, thanks a million for your help.

Regards,

asumter