CODEWITHSUNDEEP
vbaexcel
Alexander Miller
Alexander Miller

Reputation: 21

Excel / VBA: Check if Cell Reference returns 0

I wrote a VBA script that is supposed to hide rows that reference empty cells in another worksheet. So to speak, Sheet 1 contains cell A1, which is empty. Sheet 2's cell A1 contains "Sheet1!A1", but prints a 0. My script is supposed to go through all the rows and hide the ones that contain empty references, but does not seem to be doing that. It would be great if you could help me figure out my mistake. Here is my code so far:

Sub Hide()
Application.ScreenUpdating = False
Dim i As Integer
For i = 1 To 1000
If IsEmpty(Cells(1, i)) Then
Rows(i).EntireRow.Hidden = True
End If
Next i
Range("A1").Select
Application.ScreenUpdating = True
End Sub

I'd be very grateful for any kind of help.

Best regards,

Alex

Upvotes: 2

Views: 4185

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149305

Alex here is a slightly complicated way of doing this. But it works :)

Note: This will only work for Formulas which refer to sheet(s) in the same workbook or sheets of open workbooks.

Logic:

  1. This code identifies the cells which have the formulas and then loops through them
  2. Then while looping through all cells which have the formulas, it checks for the .Precedents of that cell. Here is the trickiest part. There is no easy way to get the .Dependents or .Precedents that lie on another Worksheet or Workbook. You may call it the limitation of this property. So I am using an alternative to get those :)
  3. Once I get the address of that cell, I am checking if they are empty or not and then based on the condition, I am hiding the cell.

CODE

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range, acell As Range, bcell As Range

    '~~> This is the sheet which has the formula
    Set ws = Sheets("Sheet1")

    With ws
        '~~> Get the address of all the cells which have formulas
        Set rng = .Cells.SpecialCells(xlCellTypeFormulas)

        '~~> Loop through the cells
        For Each acell In rng
            If acell.EntireRow.Hidden = False Then
                If acell.Value = 0 Then
                    On Error Resume Next
                        '~~> Clear any precedents/dependent arrows if any
                        .ClearArrows
                        '~~> Show precedents
                        acell.ShowPrecedents
                        '~~> Navigate to the relevant cell in the other worksheet
                        acell.NavigateArrow True, 1
                        '~~> Compare address and name to check if they are not from ws
                        If ActiveCell.Address <> rng.Address Or ActiveCell.Worksheet.Name <> .Name Then
                            Set bcell = Sheets(ActiveCell.Worksheet.Name).Range(ActiveCell.Address)
                            '~~> Check if it not empty
                            If Len(Trim(bcell.Value)) = 0 Then
                                '~~> If empty, hide the row
                                .Rows(acell.Row).EntireRow.Hidden = True
                            End If
                        End If
                        '~~> Clear any precedents/dependent arrows if any
                        .ClearArrows
                        .Activate
                    On Error GoTo 0
                End If
            End If
        Next
    End With
End Sub

SNAPSHOT

Sheet 1 before running the macro

enter image description here

This is how Sheet 2 Looks

enter image description here

And this is how the Sheet1 looks after the macro

enter image description here

Upvotes: 1

barrowc
barrowc

Reputation: 10679

Using Cells(1, i) in a loop means that you are working along the columns in row 1 rather than down the rows in column A. I would suspect you want Cells(i, 1) instead.

As the calls to Cells and Rows are unqualifed, they will refer to the active sheet. From your description, it's not 100% clear whether both should refer to the same sheet. It's obvious, for example, that Sheet2!A1 cannot be empty as it contains a formula referring to Sheet1!A1.

You may, therefore, want to check for empty cells on Sheet1 but actually hide the rows on Sheet2. This would change the calls to Worksheets("Sheet1").Cells(i, 1) and Worksheets("Sheet2").Rows(i)

If things are more complicated and you need to both check for emptiness and hide rows on Sheet2 then you will either need to hide all rows with a zero value in column A (which may be a problem if any of the rows on Sheet1 actually contain zero as a value) or alter the formulas on Sheet2 to explicitly deal with empty cells on Sheet1.

For example, the formula in Sheet2!A1 could be: =IF(ISBLANK(Sheet1!A1),#N/A,Sheet1!A1)

The check in your macro would then be:

If IsError(Cells(i, 1).Value) Then
  If (Cells(i, 1).Value = CVErr(xlErrNA)) Then
    Rows(i).EntireRow.Hidden = True
  End If
End If

You could just use the IsError check but that may hide genuine errors with the source data (e.g. division by zero)

Upvotes: 0

Related Questions