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?
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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,800
Office Version
  1. 2016
  2. 2010
  3. 2007
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,217
Messages
5,594,887
Members
413,947
Latest member
gizmolucy

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