Macro Delete All Rows Except for certain cells

[jaime1182]

Dear all

I was hoping for some help with deleting certain columns

My current macro is this:

Rows(3 & ":" & Rows.Count).ClearContents
Range("D2").Select

This deletes everything from Row 3 onwards. However, I have fixed data I want to keep in A3:B9

Is there a way to modify it so that I can delete only from Cell C3 : (variable column)(last row) thereby essentially keeping A3:B9?

Many thanks in advance!

 

[Logit]

.

Option Explicit




Sub clrContentsExcept()


    Dim Rng As Range
    Dim rCell As Range
    Dim Addr As String
    Dim upDte As Boolean
    On Error Resume Next
    Addr = Application.ActiveWindow.RangeSelection.Address
    Set Rng = ActiveSheet.Range("A3:B9")
    If Rng Is Nothing Then Exit Sub
    upDte = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For Each rCell In ActiveSheet.UsedRange
        If Intersect(rCell, Rng) Is Nothing Then
            rCell.Clear
        End If
    Next
    Application.ScreenUpdating = upDte
End Sub

 

[jaime1182]

Logit, that worked perfectly!

Is there a way to keep rows 1 and 2?

Ie:

 Set Rng = ActiveSheet.Range("A3:B9") & rows(2:3)

Would that work?

 

[JoeMo]

This will leave everything above row 3 and A3:B9 intact, while clearing the used range from C3 on.

Sub ClearAllExcept()
Dim lR As Long, lC As Long
lR = Cells.Find("*", , xlFormulas, xlWhole, xlByRows, xlPrevious).Row
lC = Cells.Find("*", , xlFormulas, xlWhole, xlByColumns, xlPrevious).Column
Range("C3", Cells(lR, lC)).ClearContents
Range("D2").Select
End Sub

 

[JoeMo]

Logit, that worked perfectly!

Is there a way to keep rows 1 and 2?

Ie:

 Set Rng = ActiveSheet.Range("A3:B9") & rows(2:3)

Would that work?

See post #4

 

[jaime1182]

Wow - everyone is so quick and fast today

Imma need caffeine to wake up!

 

[Logit]

.
In my macro you can change this line :

[COLOR=#333333]Set Rng = ActiveSheet.Range("A3:B9")[/COLOR]

to this line :

Set Rng = ActiveSheet.Range("A1:B9")