VBA Excel – Goto Function


December 30th, 2015 by admin admin

In one of the projects that I was working on, I wrote a set of Macros VBA code to select and highlight the cells that I want to keep. It behaves the same way as when the GOTO function is applied manually. The following code is what I used to highlighted the filtered cells yellow:

            Set rCcells = Selection.SpecialCells(xlCellTypeVisible)
            With rCcells
                .Interior.Color = RGB(255, 255, 0)
            End With

It was working perfectly well until I encounter a case when the filtered result returns zero row. The macros highlighted all the hidden rows instead. After searching high and low for the code to capture this scenario, I finally found that the way to do it is to use the COUNTIF function to count the number of records that the filtered results is likely to generate. This exceptional scenario that I encountered can be captured easily if the COUNTIF function returns the value zero which is zero count. To make it work perfectly in your codes, you need to run the COUNTIF first. If there is more than one count, then go ahead with the GOTO function. The codes to do that is given below. In the code below, I assume that I am trying count how many cells in K2:K100 contain the number 100. If there is at least one, continue with the GOTO Function. If not, skip it.

        xx = Application.WorksheetFunction.CountIf(ActiveSheet.Range("K2:K100"), "100")
        'Highlight cells yellow if auto-filter returns at least one row
        If xx > 0 Then
            ActiveSheet.Range("K2:K" & dlast_row).Select
            Set rCcells = Selection.SpecialCells(xlCellTypeVisible)
            With rCcells
                .Interior.Color = RGB(255, 255, 0)
            End With
        End If

 

Leave a Reply

You must be logged in to post a comment.

s2Member®