VBA code to hide rows

[mgchurch77]

Description of the worksheets:

For simplicity sake I'll describe my workbook as only having two worksheets.

A "source" worksheet and a "destination" worksheet. The "source" worksheet consists of column A which are names of customers and column B consists of the result of a calculation fusing data from other sheets which could be any number from 1-12.

The "destination" sheet has a certain cell (we'll call that the target cell) where the user "me" can input a number from 1-12. Column A of the destination page matches the number from the target cell to column B of the source page to determine whether or not to pull the customer name from column A of the source page into column A of the destination page. If the number from the target cell matches column B of the destination page, the name is pulled, if there is not a match the answer returned is false.

Code needed:

The code would run on the destination page as outlined above. What I need it to do is to hide the rows where the answer for that certain row in column A of the destination page is "false". Thanks in advance for anyone's help.

 

[evnoort]

Hi mgchurch77,

Didn't read your post entirely but this code hides a row if it reads a "False"
You can add a sheets("name" before the cells if the cell is on another sheet.

Good luck

Private Sub CommandButton1_Click()
Dim row As Integer, col As Integer

col = 3

For row = 1 To 10
    If Cells(row, col).Value = "False" Then
        ActiveSheet.Rows(row).Hidden = True
    Else
        ActiveSheet.Rows(row).Hidden = False
    End If

Next

End Sub

 

[mgchurch77]

Thanks evnoort, however the code didn't seem to do anything. I did change the column # in the code to 1 and the range of rows to 1:700. I also noticed that this code references a command button which I do not have nor do I want. Any other ideas.

 

[tigeravatar]

mgchurch77,

Give the following a try:

Sub tgr()
    Dim rngFalse As Range
    With Intersect(Sheets("destination").UsedRange, Sheets("destination").Columns("A"))
        .AutoFilter 1, False
        On Error Resume Next
        Set rngFalse = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        .AutoFilter
    End With
    If Not rngFalse Is Nothing Then rngFalse.EntireRow.Hidden = True
End Sub

Hope that helps,
~tigeravatar

 

[mgchurch77]

Thanks tigeravatar. Still nothing. I changed the "destination" sheet reference to "Target List Report" which is the actual name of the "destination" sheet. Other than that, I posted the code directly to the sheet and doesn't seem to do anything.

 

[mgchurch77]

FYI, I guess the code could do a "hide" instead of a delete if easier. As long as the hide would hide from printing as well.

 

[Snakehips]

Try something like this....

Sub HideFalse()

Col = 1
With ActiveSheet
Application.ScreenUpdating = False

  For r = 1 to 700
      If .Cells (r,Col).Value = FALSE Then .Cells(r,Col).EntireRow.Hidden = True
  Next r
Application.ScreenUpdating = True

End With

End Sub

To Unhide....

Sub UnhideFalse ()
ActiveSheet.Range("A1:A700").EntireRow.Hidden = False
End Sub

I have assumed that the FALSE is Boolean and not "False" as a string.
If you do not want a control button then assign keyboard shortcuts.
NB. Code needs to reside in a Module.

 

[Snakehips]

I have just thought.
If you assign a shortcut you will want to change 'ActiveSheet' to Sheets("Target List Report") so that you do not accidently process the the wrong sheet.

Alternatively, add this at the start of the code....

If Not ActiveSheet.Name = "Target List Report" Then Exit Sub

 

[mgchurch77]

Thanks from across the Atlantic.