CODEWITHSUNDEEP
excelvba
Kohlkopf
Kohlkopf

Reputation: 43

Using code only for certain rows

I'm looking for a way to exclude some rows out of my function. This is my code so far:

Dim W As Worksheet
Set W = ActiveSheet

Dim Last As Integer
Last = W.Range("Z2").End(xlToLeft).Column

If Last = 1 Then Exit Sub


Dim i As Integer
Dim j As Integer

For i = Last + 1 To 11

    W.Cells(1, i).ClearContents

    For j = 3 To 6
        W.Cells(j, i).ClearContents
    Next j

    For j = 8 To 12
        W.Cells(j, i).ClearContents
    Next j

    For j = 14 To 15
        W.Cells(j, i).ClearContents
    Next j

    For j = 18 To 22
        W.Cells(j, i).ClearContents
    Next j

    For j = 24 To 25
        W.Cells(j, i).ClearContents
        Next j

Next i

I am trying to shorten the code and tried using an array but without success. Any help would be appreciated!

Upvotes: 1

Views: 30

Answers (2)

user4039065
user4039065

Reputation:

Try,

dim lc as long
with w
    lc  = .Range("Z2").End(xlToLeft).Column
    if lc = 1 then exit sub
    application.intersect(.range("1:1, 3:6, 8:12, 14:15, 18:22, 24:25"), _
                          .range(.columns(lc+1), .columns(11))).clearcontents
end with

Upvotes: 3

Chronocidal
Chronocidal

Reputation: 8056

Two points that can make this simpler:

1) Range(Cell1, Cell2) creates a rectangle between the 2 corners. This lets you eliminate the outer loop.
2) Sometimes it is easier to exclude rows from a list instead of including every area. There are several ways to do this, but a deliminated text string combined with InStr is often a basic option.

Dim W As Worksheet
Set W = ActiveSheet

Dim Last As Integer
Last = W.Range("Z2").End(xlToLeft).Column

If Last = 1 Then Exit Sub

Dim i As Integer

For i = 1 to 25
    If InStr("|2|7|13|16|17|23|", "|" & Cstr(i) & "|") = 0 Then
        W.Range(W.Cells(i, Last+1), w.Cells(i, 11)).ClearContents
    End If
Next i

Upvotes: 2

Related Questions