CODEWITHSUNDEEP
excel
Coty Cray
Coty Cray

Reputation: 3

Hiding rows using a macro

I've got this project I'm working on where I have a "Data" sheet and am pulling the data in to "Sheet 2". I want a row on "Sheet 2" to automatically hide/unhide if there is no data within a specific cell in that row.

I am very new to macros and coding in general. I have tried looking this up but and following the tutorials on YouTube but none of them go too in-depth as to why you select certain things and I haven't had any luck getting them to work.

My question are.:

  1. Would a macro that is effecting "Sheet 2" while I am on "Data", be placed within "Sheet 2" or within "ThisWorkbook"?

  2. Before you write the macro, you need to select the drop downs "Workbook" and then I would assume you are detecting a change so would "SheetChange" be correct?

  3. Will a macro detect a change within a cell that is hidden and then can that cell's row be unhidden?

  4. Lastly, What the heck do I even write for the to detect a 0 and hide/unhide cells, I haven't gotten any of them to do anything so far?

Thank you for any help you guys can give.

Upvotes: 0

Views: 58

Answers (1)

JNevill
JNevill

Reputation: 50263

To answer your questions:

  1. Would a macro that is effecting "Sheet 2" while I am on "Data", be placed within "Sheet 2" or within "ThisWorkbook"?

It doesn't matter where you place the code unless the code is triggered by a built in event like Worksheet_Change(). In that case then it must reside within the worksheet that you are wanting to detect changes in. If it's not event driven then I would place the code in it's own module. To each their own though.

  1. Before you write the macro, you need to select the drop downs "Workbook" and then I would assume you are detecting a change so would "SheetChange" be correct?

It would be appropriate if you wanted the code contained in that subroutine to be executed whenever there is a change to that worksheet. You could also trigger code through a user action like a button click or a double click or sheet activation, etc.

  1. Will a macro detect a change within a cell that is hidden and then can that cell's row be unhidden?

Sure! Why not? As an example:

Private Sub Worksheet_Change(ByVal Target As Range)

    'Detect if the change was in a cell we care about (it can be hidden, VBA don't care)
    If Not Intersect(Target, Column(3)) Is Nothing Then
        'Something changed in Column C! Unhide whatever row had the change
        Target.EntireRow.Hidden = False
    End If

End Sub
  1. Lastly, What the heck do I even write for the to detect a 0 and hide/unhide cells, I haven't gotten any of them to do anything so far?

Using that last example to detect if a particular column experienced a change, detect if it was just a single cell, detect if that single cell is now a 0 and hide it's row:

Private Sub Worksheet_Change(ByVal Target As Range)

    'Detect if the change was in a cell we care about (it can be hidden, VBA don't care)
    If Not Intersect(Target, Column(3)) Is Nothing Then
        'Something changed in Column C!
        If Target.Cells.Count = 1 Then
            If Target.Value = "0" Then
                Target.EntireRow.Hidden = True
            End If
        End If
    End If

End Sub

Stick that in whatever worksheet you wanting to detect the change. Change that first If so that you are only detecting changes in whatever range you anticipate that change to happen in (I chose Column C as an example).

Upvotes: 2

Related Questions