Results 1 to 12 of 12

Collum Delete Macro Needs to run twice to do it's job!?

Threaded View

  1. #1
    Registered User
    Join Date
    02-19-2013
    Location
    Thailand
    MS-Off Ver
    Excel 2007
    Posts
    34

    Collum Delete Macro Needs to run twice to do it's job!?

    I'm using a macro to delete all rows where the value in column K is 1 or blank, but leave if it's 0.

    Sub deleteRow()
       Range("k2").Select
       
       Dim Rw_count As Integer
       Dim K As Integer
       
       Rw_count = ActiveSheet.UsedRange.Rows.Count
       
       For K = 1 To Rw_count
          
      If ActiveCell > 0 Then
      Selection.EntireRow.Delete
      ElseIf ActiveCell = "" Then
      Selection.EntireRow.Delete
      Else
      Selection.Offset(1, 0).Select
       
      End If
      
      Next K
      
      
    End Sub
    On the first run it will delete the rows where cell value is 1 but leaves all rows where cell value is blank. On the second run it takes care of blank ones too.
    Note, even if there are no 1 values the macro still needs to run twice to get the job done. Messy solution is obviously to just write it twice, but i'd like to know whats going on.
    Any thoughts?
    Cheers

    TLDR

    Quote Originally Posted by JosephP View Post
    I see the confusion-the rows are being deleted but the used range is not reset. add
    activesheet.usedrange
    as the last line of code
    Last edited by exarranum; 04-23-2013 at 09:43 PM.

Thread Information

Users Browsing this Thread

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

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