how to apply a VBA to multiple rows in MS excel?

Question

I am very novice at this, I need to apply the below to rows with VBA, can anyone advise please.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("A59")) Is Nothing Then
        Range("B59:E59").ClearContents

    End If

     If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("B59")) Is Nothing Then
        Range("C59:E59").ClearContents

    End If

    If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("C59")) Is Nothing Then
        Range("D59:E59").ClearContents

    End If

    If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("D59")) Is Nothing Then
        Range("E59").ClearContents

    End If

End With

I have five dependent dropdown list columns each one is dependent of the previous. I am trying to reset the cells once the previous column selection is changed. The above works fine but I do not know how to apply it to all rows or up to 10000 rows for example.

I would very much appreciate the guidance on this

Many thanks G

Hm... use loop depending on your preferences.

lowak
– lowak

2014-01-23 12:56:36 +00:00
Commented Jan 23, 2014 at 12:56 — lowak
– lowak, Commented Jan 23, 2014 at 12:56
Don't know how to add a loop.

user3227790
– user3227790

2014-01-23 13:49:09 +00:00
Commented Jan 23, 2014 at 13:49 — user3227790
– user3227790, Commented Jan 23, 2014 at 13:49

Makah · Accepted Answer · 2014-01-23 13:02:00Z

1

If you don't want to use a loop, you can try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Cells.Count > 1 Then Exit Sub

  If Target.Row = 59 then
    If not IsEmpty(Target) then
      Range(Cells(59,Target.Column +1),Cells(59,"E")).ClearContents
    End If  
  End If
End Sub

answered Jan 23, 2014 at 13:02

Makah

4,5233 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3227790 Over a year ago

Many thanks for this, but this still wont solve the rows and will also delete any text in after column E. A loop formula would be great. Many thanks G

Makah Over a year ago

I didn't understand your problem. Can you provide a data example that this algorithm don't fit.

Dmitry Pavliv · Accepted Answer · 2014-01-23 14:09:05Z

0

Try this one:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Cells.Count > 1 Then Exit Sub

        If .Row >= 59 And .Row <= 10000 _
            And .Column <= 4 Then
            Range(Cells(.Row, .Column + 1), Cells(.Row, 5)).ClearContents
        End If
    End With
End Sub

edited Jan 23, 2014 at 14:09

answered Jan 23, 2014 at 14:03

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Collectives™ on Stack Overflow

how to apply a VBA to multiple rows in MS excel?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related