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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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
 

exceluser2007

Active Member
Joined
Nov 21, 2007
Messages
365
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:

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,875
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
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:

exceluser2007

Active Member
Joined
Nov 21, 2007
Messages
365
As variant:
Rich (BB code):

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

I like that ZVI, I think its more readable.

Thanks!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,983
Messages
5,834,709
Members
430,311
Latest member
JAC0617

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
Top