A More efficient WITH..END WITH

exceluser2007

Active Member
Joined
Nov 21, 2007
Messages
365
Hi all,

I have the following 2 lines of code:

Code:
        Range(RngSheetListStart, RngSheetListStart.End(xlDown)).Clear
        Range(RngSheetListStart.Offset(0, 1), RngSheetListStart.Offset(0, 1).End(xlDown)).Clear

I then wanted to make it more efficient by changing it to a WITH..END WITH construct as follows:

Code:
    With RngSheetListStart

        Range([B]RngSheetListStart[/B], .End(xlDown)).Clear
        Range([B]RngSheetListStart[/B].Offset(0, 1), .Offset(0, 1).End(xlDown)).Clear

    End With

Q1: Is there any way to more efficiently include the bold RngSheetListStart as part of the With..End With constructs?

Q:2 Also, is there a better way to do the above?That is, based on a single cell range "RngSheetListStart" (select down and clear all). Also select the cellnext to the "RngSheetListStart" and select down and clear all?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
What does "efficiently" mean? As far as I am concerned, is the code understandable? Maintainable? If so, you are good.

If you insist, you could tweak it
Code:
    Dim RngSheetListStart As Range
    With RngSheetListStart

    .Range(.Cells(1, 1), .End(xlDown)).Clear
    With .RngSheetListStart.Offset(0, 1)
    .Range(.Cells(1, 1), .End(xlDown)).Clear
        End With
        End With
 
Upvote 0
What does "efficiently" mean? As far as I am concerned, is the code understandable? Maintainable? If so, you are good.

If you insist, you could tweak it
Rich (BB code):
    Dim RngSheetListStart As Range
    With RngSheetListStart

    .Range(.Cells(1, 1), .End(xlDown)).Clear
    With .RngSheetListStart.Offset(0, 1)
    .Range(.Cells(1, 1), .End(xlDown)).Clear
        End With
        End With

Hi tushar, first off thanks for your reply.

Comedy of errors today as I replied in the otehr duplicate thread, but lets continue here.

When I said efficient, I meant to utilise the WITH handler to full capacity, so in between the WITH-END..WITH handler have no mention of the "RngSheetListStart" variable at all, something like:

Rich (BB code):
    With RngSheetListStart

        Range(., .End(xlDown)).Clear
        Range(.Offset(0, 1), .Offset(0, 1).End(xlDown)).Clear

    End With
The bold line is incorrect, but it is what I was trying to get at. I realise from your post that it is hard to wrap in one WITH..END WITh handler.

Thanks for your help.
 
Last edited:
Upvote 0
May be like this?
Code:
With RngSheetListStart
    Range(.Cells, .End(xlDown)).Clear
    Range(.Offset(0, 1), .Offset(0, 1).End(xlDown)).Clear
End With
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top