Delete all EXCEPT named range

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi, this is REALLY frustrating me.
    I've been trying to have a macro that removes the bottom row of a sheet. I have tried literally every solution I've seen and none of them will work. They all think the bottom row is 25, or 220, (when they're all blank). The bottom row should be 4.


    With that said, I've decided to try a different method. I've created a named range "RIFRange", which will select my table, not including the bottom row.


    =OFFSET('RIF Events'!$A$1,0,0,COUNTA('RIF Events'!$A:$A)-1,COUNTA('RIF Events'!$1:$1))


    What I'd like to do now, is clear the inverse of my named range. I've seen some people suggesting you can store the named range content in memory, clear everything and then repaste the content. I'm just having a hard time with my code recognizing the range.


    Any help would be greatly appreciated!

    Re: Delete all EXCEPT named range


    An example of some of the code I've tried and found (with no luck) is this:
    Also, the named range is open to the workbook

    Code
    Dim v As Variant
     With Range("RIFRange")
        v = .Formula
        Cells.ClearContents
        .Formula = v
     End With


    Re: Delete all EXCEPT named range


    So with that said, I tried this and it did something...


    Code
    Dim v As Variant 
With Worksheets("RIF Events").Range("RIFRange") 
    v = .Formula 
    Cells.ClearContents 
    .Formula = v 
End With


    It didn't ERROR, but it DID wipe out Sheet1. (didn't touch Sheet9 (RIF Events))

    Re: Delete all EXCEPT named range


    Okay, THIS code is actually showing me the correct row value - I just need to figure out how to delete the row now.


    Code
    Sheet9.Select
Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row




MsgBox lastRow

    Re: Delete all EXCEPT named range


    Ha. SO, I figured it out


    Code
    Sheet9.Select
Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Sheet9.Rows(lastRow).entirerow.Delete

    Re: Delete all EXCEPT named range


    Maybe this. :cool:


    Code
    Sub ClearSheet()
Dim v As Variant
    With Worksheets("RIF Events")
        v = .Range("RIFRange").Formula
        .Cells.ClearContents
        .Range("RIFRange").Formula = v
    End With
End Sub

    Bruce :cool:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login