Specific macro or VBA code to hide and unhide rows

**jaslake** · 11-05-2014, 12:46 PM

Hi louvaek

Here's your File with the Code embedded...

**louvaek** · 11-05-2014, 12:59 PM

Thanks Jaslake.

But something doesn't work. If I add a value, a row unhides indeed. But if I delete it again, it doesn't hide again

What's the diff. between your solution and Olly's? Which one should I use if I really have lot's of names in that list, and lot's of names that should be added by users?

Grtz
Kristof

**jaslake** · 11-05-2014, 01:12 PM

Hi Kristof

The downside of Olly's Code is that the range of Cells is fixed. If that works for you then use it.

If Not Intersect(Target, Range("B12:B17")) Is Nothing Then

I'll look at this

But if I delete it again, it doesn't hide again

**louvaek** · 11-05-2014, 01:18 PM

Well Olly's works, but I just tested something and the code missed it effect afterwards. I deleted a row (a name that should not longer be in the list) in the B3:B11 range.
After that, the code doesn't work anymore in B12 (B13 : B17 still works). If I delete another row, B13 doesn't work anymore.

So Olly's code is clean and simple, but should be manually changed each time I delete a row in the sheet.
I guess the code should have something with 'row()' included? I know a lot about formula's, but I am a real VBA newbie :-)

**jaslake** · 11-05-2014, 01:30 PM

Hi Kristof

There's a bit of mind numbing logic going on here, see if this works as required

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim OldValue As String, NewValue As String
  If Target.Cells.Count > 1 Then Exit Sub
  If Not Target.Column = 2 Then Exit Sub
  On Error GoTo Reset
  With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Undo
    OldValue = Target.Value  'store old value
    If Not OldValue = "" Then
      .Undo
      If Range(Target.Address).Offset(1, 0).Value = "" _
         Or Range(Target.Address).Offset(-1, 0).Value = "" Then
        Range(Target.Address).EntireRow.Hidden = True
      End If
      .ScreenUpdating = True
      .EnableEvents = True
      Exit Sub
    Else
      .Undo
      NewValue = Target.Value     'store new value
      Range(Target.Address).Offset(1, 0).EntireRow.Hidden = False
      .EnableEvents = True
      .ScreenUpdating = True
    End If
  End With
Reset:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
End Sub

**louvaek** · 11-05-2014, 02:20 PM

Jaslake,

The unhiding works perfect. If I add a value in B13, B14 shows. If I add a value in B15, B15 shows.
But when I delete B14, B15 does not unhide, it's B14 that hides ... and B15 stays visible.

**jaslake** · 11-05-2014, 03:25 PM

Hi Kristof

When you say "delete", are you deleting a Row or are you clearing the contents of the Cell?

**louvaek** · 11-05-2014, 03:39 PM

If I delete the value (or values) in the B12:B17 range.
Deleting rows works fine with your VBA code.

**jaslake** · 11-05-2014, 04:15 PM

Hi Kristof

If I understand the issue

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim OldValue As String, NewValue As String
  If Target.Cells.Count > 1 Then Exit Sub
  If Not Target.Column = 2 Then Exit Sub
  On Error GoTo Reset
  With Application
    .ScreenUpdating = False
    .EnableEvents = False
    NewValue = Target.Value     'store new value
    .Undo
    OldValue = Target.Value  'store old value
    If Not OldValue = "" Then
      .Undo
      If NewValue = "" And Range(Target.Address).Offset(1, 0).Value = "" Then
        Range(Target.Address).Offset(1, 0).EntireRow.Hidden = True
        GoTo Reset
      End If
      If Range(Target.Address).Offset(1, 0).Value = "" _
         And Range(Target.Address).Offset(-1, 0).Value = "" Then
        Range(Target.Address).EntireRow.Hidden = True
        GoTo Reset
      End If
    Else
      .Undo
      Range(Target.Address).Offset(1, 0).EntireRow.Hidden = False
    End If
  End With
Reset:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
End Sub

Specific macro or VBA code to hide and unhide rows

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: Specific macro or VBA code to hide and unhide rows

Re: Specific macro or VBA code to hide and unhide rows

Re: Specific macro or VBA code to hide and unhide rows

Re: Specific macro or VBA code to hide and unhide rows

Re: Specific macro or VBA code to hide and unhide rows

Re: Specific macro or VBA code to hide and unhide rows

Re: Specific macro or VBA code to hide and unhide rows

Re: Specific macro or VBA code to hide and unhide rows

Re: Specific macro or VBA code to hide and unhide rows

Thread Information

Users Browsing this Thread

Similar Threads

use macro to find and replace specific code in vba

[SOLVED] Code to run a Macro on a specific day of the week at a specific time.

[SOLVED] Macro code to Copy specific rows into specific columns

vba code to apply a macro to a specific tab

Assistance with Creating a Specific Macro using VBA code

Bookmarks

Bookmarks

Posting Permissions