+ Reply to Thread
Results 1 to 5 of 5

Making range delete code more efficient

Hybrid View

Geoff. Making range delete code more... 12-10-2014, 04:36 PM
BS Singh Re: Making range delete code... 12-10-2014, 04:49 PM
Geoff. Re: Making range delete code... 12-10-2014, 05:14 PM
BS Singh Re: Making range delete code... 12-11-2014, 01:38 AM
Geoff. Re: Making range delete code... 12-17-2014, 04:59 PM
  1. #1
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Making range delete code more efficient

    Hello everyone,

    I would appreciate some help with making the following code more efficient (got it online). It works as it is but I have two issues with it that I can't seem to solve:



    1) I can't seem to be able to replace this code
    .Value = "2014-09-05_2014-10-03"
    with something more flexible like the following. Am I doing something wrong or is it the way the macro is made that prevents this change?
    .Value = .Range("A1")


    2) I read that structuring a range reference in the following way was "frowned upon." Why is that and how do I improve this?
    Range("M" & Lrow & ":" & "P" & Lrow)


    If there is anything else you see that can improve the code, please let me know. The entire code is below.

    Sub Loop_Example()
        Dim Firstrow As Long
        Dim Lastrow As Long
        Dim Lrow As Long
        
       
    
    
            With Worksheets("ref")
    
            'Set the first and last row to loop through
            Firstrow = 2
            Lastrow = .Cells(.Rows.Count, "P").End(xlUp).Row
    
    
            'We loop from Lastrow to Firstrow (bottom to top)
            For Lrow = Lastrow To Firstrow Step -1
    
                'We check the values in the P column in this example
                With .Cells(Lrow, "P")
    
                    If Not IsError(.Value) Then
    
                        If .Value = "2014-09-05_2014-10-03" Then Range("M" & Lrow & ":" & "P" & Lrow).Delete (xlShiftUp)
                        'This will delete cells in the M:P column range where at lrow, .Value is the same as above
                        'in Column P, case sensitive.
    
                    End If
    
                End With
    
            Next Lrow
    
        End With
    
        
    
    End Sub

    Thanks!

    Geoff.

  2. #2
    Forum Contributor
    Join Date
    08-17-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    132

    Re: Making range delete code more efficient

    Certainly this can be modified..just attach your spreadsheet with some data on it.

    Regards,

  3. #3
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Making range delete code more efficient

    Hello BS Singh, thank you for taking the time to help me out.

    Here is the file that I was testing this code on: del_range.xlsx

    I made a slight change in the code so here it is again:
    Sub Loop_Example()
        Dim Firstrow As Long
        Dim Lastrow As Long
        Dim Lrow As Long
        
       
    
    
        With Worksheets("ref")
    
            'Set the first and last row to loop through
            Firstrow = 2
            Lastrow = .Cells(.Rows.count, "P").End(xlUp).Row
    
    
            'We loop from Lastrow to Firstrow (bottom to top)
            For Lrow = Lastrow To Firstrow Step -1
    
                'We check the values in the A column in this example
                With .Cells(Lrow, "P")
    
                    If Not IsError(.Value) Then
    
                        If .Value = "2014-09-05_2014-10-03_Sinc" Then Range("M" & Lrow & ":" & "P" & Lrow).Delete (xlShiftUp)
                        'This will delete cells in the M:P column range where at lrow, .Value is the same as above
                        'in Column P, case sensitive.
    
                    End If
    
                End With
    
            Next Lrow
    
        End With
    
        
    
    End Sub
    Regards,

    Geoff.

  4. #4
    Forum Contributor
    Join Date
    08-17-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    132

    Re: Making range delete code more efficient

    Hi Geoff,

    This should work for you:

    Sub Loop_Example()
        
        Dim rng As Range
        Dim ws As Worksheet
        Dim cel As Range
        
        Set ws = ThisWorkbook.Sheets("ref")
        Set rng = ws.Range("P1:P" & ws.Cells(Rows.Count, "P").End(xlUp).Row)
        
            For Each cel In rng
                If cel.Value = ws.Range("S1").Value Then Range("M" & cel.Row & ":" & "P" & cel.Row).Delete (xlShiftUp)
            Next
    End Sub
    This will compare the value of column P with value you will enter in Cell S1 and if match found then will delete the cells from M and P column.

    The comparison will take place from top to bottom rather then bottom to top. I hope this is not an issue.

    Regards,

  5. #5
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Making range delete code more efficient

    Hello BS Singh,

    Apologies for the delay in responding, I thought no replied has come in on this thread.

    Thank you for this code. I had read somewhere that going for top to bottom can be an issue for this type of exercise because the code might skip over the row just beneath the one that was found to match the criteria. I'm not sure if this is clear, if so, do you know if this is an issue in this case?

    Thanks,

    Goeff

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Making VBA Code more efficient -Filtering Data
    By greg_c in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-01-2012, 10:45 PM
  2. Help needed on making code more efficient
    By FallingDown in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-27-2011, 07:49 AM
  3. making code more Efficient !!! please help
    By virgiliocabrera in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-01-2011, 09:09 PM
  4. making code more efficient
    By JChandler22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2008, 05:11 PM
  5. Making Copy and Paste Code more efficient
    By T De Villiers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-18-2006, 11:31 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1