Resolved: How to clear all data EXCEPT columns X, Y & Z AND rows A,B,C?

Question:

I have seen this very similar question, and I think the answer by user @SQL Police is great! But I can not figure out how to modify or adapt it to produce the results I am looking for. Here is my (similar, but different) question:
I want to delete everything that is NOT in columns 24 – 26, but also not in rows 1 – 6.
I have code that can do each seperately, but not together.
To delete with specified columns:

Sub ColClear()
Dim ws As Worksheet
Set ws = ActiveSheet

ws.Range(ws.Columns(1), ws.Columns(23)).ClearContents
ws.Range(ws.Columns(27), ws.Columns(ws.UsedRange.End(xlToRight).Column)).Clear

End Sub

To delete with specified rows:

Sub RowClear()
With Sheets("SheetName")
.Rows(7 & ":" & .Rows.Count).ClearContents
End With
End Sub

But how can I do both at once? (The intersection of the two ranges, not the union)

Thanks!

Answer:

Adapt to your requirement, the basic idea is to get the range of both sides then ClearContents separately:

Option Explicit

Private Sub Test()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    With ws
        Dim bottomLeftRng As Range
        Set bottomLeftRng = .Range(.Cells(7, 1), .Cells(.Rows.Count, 23))
        
        Dim bottomRightRng As Range
        Set bottomRightRng = .Range(.Cells(7, 27), .Cells(.Rows.Count, .Columns.Count))
    End With
    
    bottomLeftRng.ClearContents
    bottomRightRng.ClearContents
End Sub

If you have better answer, please add a comment about this, thank you!

Resolved: How to clear all data EXCEPT columns X, Y & Z AND rows A,B,C?

Resolved: How do I stop the command from happening if the requirements for it to work aren’t sert Discord.js

Resolved: How to scroll bottom of div at launch?

Resolved: how to get and read an xml file in a zip file using xml.etree

Leave A Reply