Clear non-contiguous cells

[wpryan]

Hi all, I want to clear a dynamic range of non-contiguous cells. The code I'm using (or trying to use ) is:

    Dim sht1 As Worksheet
    Dim LastRowWS1 As Long
    Dim nRange As Range
    
    Set sht1 = Sheets("Data")


    LastRowWS1 = sht1.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    
    Set nRange = Range("A" & LastRowWS1 - 1), ("A" & LastRowWS1 - 3), ("B" & LastRowWS1 - 1), ("B" & LastRowWS1 - 3), _
   ("C" & LastRowWS1 - 1), ("C" & LastRowWS1 - 3), ("D" & LastRowWS1 - 1), ("D" & LastRowWS1 - 2), ("D" & LastRowWS1 - 3), _
   ("E" & LastRowWS1 - 1), ("E" & LastRowWS1 - 2), ("E" & LastRowWS1 - 3)
   
    With nRange
        .Locked = False
        .ClearContents
    End With

I'm getting a syntax error on the "set nRange" line. I tried a lot of variations, including .cells, range.cells, etc. How would I clear the data in these cells...?

 

[dmt32]

hi,

try

Set nRange = Union(Range("A" & LastRowWS1 - 1), Range("A" & LastRowWS1 - 3), Range("B" & LastRowWS1 - 1), _
                        Range("B" & LastRowWS1 - 3), Range("C" & LastRowWS1 - 1), Range("C" & LastRowWS1 - 3), _
                        Range("D" & LastRowWS1 - 1), Range("D" & LastRowWS1 - 2), Range("D" & LastRowWS1 - 3), _
                        Range("E" & LastRowWS1 - 1), Range("E" & LastRowWS1 - 2), Range("E" & LastRowWS1 - 3))

Dave

 

[Fluff]

Another way

    Set nRange = Union(Range("A" & LastRowWS1 - 1).Resize(, 5), Range("A" & LastRowWS1 - 3).Resize(, 5), Range("D" & LastRowWS1 - 2).Resize(, 2))

 

[wpryan]

That did the trick. Thanks a lot, I love you guys at mrexcel!

 

[dmt32]

That did the trick. Thanks a lot, I love you guys at mrexcel!

your welcome - glad we could help.

Many thanks for feedback

Dave

 

[wpryan]

I thought I was done but evidently not... What I want to do is to copy a range of cells to another location, and if the last row in the range contains data, then to define a given range to delete, and if not then define a different range to delete. The code is:

    If ((Range("A" & LastRowWS1) <> "") Or (Range("B" & LastRowWS1) <> "") Or (Range("C" & LastRowWS1) <> "") _
        Or (Range("D" & LastRowWS1) <> "") Or (Range("E" & LastRowWS1) <> "")) Then
            Set nRange = Union(Range("A" & LastRowWS1 + 1), Range("A" & LastRowWS1 - 1), _
                Range("B" & LastRowWS1 + 1), Range("B" & LastRowWS1 - 1), _
                Range("C" & LastRowWS1 + 1), Range("C" & LastRowWS1 - 1), _
                Range("D" & LastRowWS1 + 1), Range("D" & LastRowWS1), Range("D" & LastRowWS1 - 1), _
                Range("E" & LastRowWS1 + 1), Range("E" & LastRowWS1), Range("E" & LastRowWS1 - 1))
            Else:
            Set nRange = Union(Range("A" & LastRowWS1), Range("A" & LastRowWS1 - 2), _
                Range("B" & LastRowWS1), Range("B" & LastRowWS1 - 2), _
                Range("C" & LastRowWS1), Range("C" & LastRowWS1 - 2), _
                Range("D" & LastRowWS1), Range("D" & LastRowWS1 - 1), Range("D" & LastRowWS1 - 2), _
                Range("E" & LastRowWS1), Range("E" & LastRowWS1 - 1), Range("E" & LastRowWS1 - 2))
    End If


    With nRange
        .Locked = False
        .FormulaHidden = False
        .ClearContents
    End With

If there is data or not in the "if" criteria, all the time the first "or" condition is triggered - never the second. Where am I going wrong...?
Thanks in advance...

 

[dmt32]

try this update to your code

Dim CheckRng As Range
    Set CheckRng = Range("A" & LastRowWS1).Resize(, 5)
    
    If Not Application.CountA(CheckRng) < CheckRng.Cells.Count Then
        
        Set nRange = Union(Range("A" & LastRowWS1 + 1), Range("A" & LastRowWS1 - 1), _
        Range("B" & LastRowWS1 + 1), Range("B" & LastRowWS1 - 1), _
        Range("C" & LastRowWS1 + 1), Range("C" & LastRowWS1 - 1), _
        Range("D" & LastRowWS1 + 1), Range("D" & LastRowWS1), Range("D" & LastRowWS1 - 1), _
        Range("E" & LastRowWS1 + 1), Range("E" & LastRowWS1), Range("E" & LastRowWS1 - 1))
    Else
        Set nRange = Union(Range("A" & LastRowWS1), Range("A" & LastRowWS1 - 2), _
        Range("B" & LastRowWS1), Range("B" & LastRowWS1 - 2), _
        Range("C" & LastRowWS1), Range("C" & LastRowWS1 - 2), _
        Range("D" & LastRowWS1), Range("D" & LastRowWS1 - 1), Range("D" & LastRowWS1 - 2), _
        Range("E" & LastRowWS1), Range("E" & LastRowWS1 - 1), Range("E" & LastRowWS1 - 2))
    End If
    
    
    With nRange
        .Locked = False
        .FormulaHidden = False
        .ClearContents
    End With

If does what you now want may want to give Fluffs more compact suggestion a try.

Dave

 

[pgc01]

Hi wpryan

I tried your test:

Sub Test1()
Dim LastRowWS1 As Long

LastRowWS1 = 10
MsgBox ((Range("A" & LastRowWS1) <> "") Or (Range("B" & LastRowWS1) <> "") Or (Range("C" & LastRowWS1) <> "") _
        Or (Range("D" & LastRowWS1) <> "") Or (Range("E" & LastRowWS1) <> ""))
End Sub

If there is any data in A10:E10 it returns true else false.
This seems to be what you want ???

Please explain.

 

[wpryan]

Hello, I made a dumb mistake by conditionally redefining the last row (e.g. "if A, last row = x, if B, last row = y"). I got this wrong, so the code was actually doing what it should have. I corrected the dumb mistake and all is working fine now. Thanks for your help.