Finding the Visible Rows

When you use AutoFilter, Excel simply hides the rows that do not match the current filters. If you want to process just the rows that are visible in your code, you need to look at each row in the list and decide if it is hidden or not. There is a trick to this. When referring to the Hidden property of a Range object, the Range object must be an entire row, extending from column A to column XFD, or an entire column, extending from row 1 to row 1048576. You can't use the Hidden property with a single cell or a seven-column row from the list shown in Figure 6-9.

The following code checks each row that is visible on the screen and shades the background of any row that has an invalid Revenue calculation:

Private Sub CommandButton1_Click()

Dim rngData As Range

Dim rngRow As Range

Dim dNumberSold As Double

Dim dPrice As Double

Dim dRevenue As Double

'Locate datarows

Set rngData = Sheet1.ListObjects("Table1"


'Loop through all data rows

For Each rngRow In rngData.Rows

'Only process visible rows If rngRow.EntireRow.Hidden = False Then

'Check calculation dNumberSold = rngRow.Cells(5).Value dPrice = rngRow.Cells(6).Value dRevenue = rngRow.Cells(7).Value

'If wrong, display error

If Abs(dNumberSold * dPrice - dRevenue) > 0.000001 Then rngRow.Select rngRow.Interior.ColorIndex = 3 MsgBox "Error in selected row"

End If

End If

Next rngRow End Sub

Figure 6-9

The Click event procedure for the command button first defines an object variable rngData referring to the rows of data in Tablel, excluding the Header Row, which is returned by the DataBodyRange property of the ListObject object. It then uses a For Each...Next loop to process all the rows in rngData.

The first If test ensures that only rows that are not hidden are processed. The dNumberSold, dPrice, and dRevenue values for the current row are assigned to variables, and the second If tests that the dRevenue figure is within a reasonable tolerance of the product of dNumberSold and dPrice.

Because worksheet computations are done with binary representations of numbers to an accuracy of about 15 significant figures, it is not always appropriate to check that two numbers are equal to the last decimal point, especially if the input figures have come from other worksheet calculations. It is better to see if they differ by an acceptably small amount. Because the difference can be positive or negative, the Abs function is used to convert both positive and negative differences to a positive difference before comparison.

If the test shows an unacceptable difference, the row is selected and a message is displayed. The row is also given a background color of red.

0 0


Post a comment