+ Reply to Thread
Results 1 to 25 of 25

Deleting Row in Excel VBA

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Deleting Row in Excel VBA

    Here is my code, I am attempting to delete only rows in the second sheet in the workbook called RawData. This code will delete rows in the active sheet, the problem is that the button is on page 1, not page 2... I want the data to stay on page 2.



    
    
    Sub Deleterows()
        
        Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    02-09-2012
    Location
    Mauritius
    MS-Off Ver
    Excel 2007
    Posts
    1,055

    Re: Deleting Row in Excel VBA

    Hi
    Try this:
    Sub Deleterows()
    sheets("RawData").select
    Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    End Sub

  3. #3
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Deleting Row in Excel VBA

    No need to use the select statement.

    Sub Deleterows()
    sheets("RawData").Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    End Sub

  4. #4
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Deleting Row in Excel VBA

    Quote Originally Posted by Whizbang View Post
    No need to use the select statement.

    Sub Deleterows()
    sheets("RawData").Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    End Sub
    So this will look in Column C and delete the row if empty?

    Sounds good, is there a way to make it so I can change the variable upon what is in the cell?

  5. #5
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Deleting Row in Excel VBA

    Try this to customize the variable:

    Sheets("RawData").Range("A1:H" & Sheets("RawData").Range("A1").End(xlDown).Row).AutoFilter Field:=3, Criteria1:=""
    Sheets("RawData").Range("A2:A" & Sheets("RawData").Range("A1").End(xlDown).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Sheets("RawData").Range("A1").AutoFilter
    Change to suit.

  6. #6
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Deleting Row in Excel VBA

    Ok, I look at that, how does this only select one column to look into and delete a row if a word is in said dedicated column?

  7. #7
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Deleting Row in Excel VBA

    Short answer is "The field argument".

    The long asnwer is:

    ...Range("A1:H" & Sheets("RawData").Range("A1").End(xlDown).Row)...
    This bit just defines the table. So, it is applying autofilter to columns A:H down to the last used row in A1. Assuming that A1 has contiguous data down to row 200, the range would be A1:H200. You would need to modify the columns to suit your workbook.

    Field:=3
    This is the bit that identifies which column to compare values. 3 is the third column of the table defined (A1:H200). If the table was B1:J200 and you wanted to search column C, you would use Field:=2.

    ...Criteria1:=""
    This is where you define the variable you want to match. This can actually be fairly complex. You can use wildcards to make a "contains" type of evaluation, or you could do a less than or greater than scenario.

    Sheets("RawData").Range("A2:A" & Sheets("RawData").Range("A1").End(xlDown).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    This bit is fairly straight forward in that it deletes the visible rows in the table. One thing to notice, though, is that I started the table at A2, not A1. This is because the header row would be visible, and we don't want to delete that. Also, I don't need to check any columns other than A. If A is visible, then all columns in that row are visible.

    Sheets("RawData").Range("A1").AutoFilter
    This bit just turns Autofilter off for the table.

  8. #8
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Deleting Row in Excel VBA

    Is there a way to simplify it? I only ask because if I come back to it in a month I want to be able to come back to it and see with ease that line x defines the column, line y defines what it is looking for.

  9. #9
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Deleting Row in Excel VBA

    FirstColumn = "A"
    LastColumn = "H"
    SearchColNumber = 3 'Column number from FirstColumn.  e.g. If FirstColumn is B and you want to search column C, you would use number 2
    SearchCriteria = "" 'e.g ">0", "=Test*", etc.
    
    Sheets("RawData").Range(FirstColumn & "1:" & LastColumn & Sheets("RawData").Range(FirstColumn &"1").End(xlDown).Row).AutoFilter Field:=SearchColNumber, Criteria1:=SearchCriteria
    Sheets("RawData").Range(FirstColumn & "2:" & FirstColumn & Sheets("RawData").Range(FirstColumn & "1").End(xlDown).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Sheets("RawData").Range(FirstColumn & "1").AutoFilter
    There, now all your criteria is set up at the top of the procedure and you don't need to worry about figuring the rest out.
    Last edited by Whizbang; 04-06-2012 at 01:50 PM.

  10. #10
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Deleting Row in Excel VBA

    Why put a limitation on first and last column, wouldn't it be easier to have something along the lines of

    Range("B:B").Select

  11. #11
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Deleting Row in Excel VBA

    I was thinking something along the lines of this: I know this probably won't work but you'll get the idea.

    Sub tgr()  
    
      Dim wsB As Worksheet 'BackOrder
        Dim wsJ As Worksheet 'Jobs List
        Dim wsA As Worksheet 'Archive
        Dim LastRow As Long
        
        Set wsB = Sheets("BackOrder")
        Set wsJ = Sheets("Jobs List")
        Set wsA = Sheets("Archive")
        
    
    With Intersect(wsB.UsedRange, wsB.Columns("C"))
             .Copy wsB.Cells(Rows.Count, "B").End(xlUp).Offset(1)
            .AutoFilter 1, ""
            .EntireRow.Delete
    
    End Sub

  12. #12
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Deleting Row in Excel VBA

    SearchColumn = "C"
    SearchCriteria = "" 'e.g ">0", "=Test*", etc.
    
    With Sheets("RawData")
        .Range(SearchColumn & "1").EntireColumn.AutoFilter Field:=1, Criteria1:=SearchCriteria
        .Range(SearchColumn & "2:" & SearchColumn & .Range(SearchColumn & "1").End(xlDown).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Range(SearchColumn & "1").AutoFilter
    End With

  13. #13
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Deleting Row in Excel VBA

    This is about as simple as I can make it.

    SearchColumn = "C"
    SearchCriteria = "" 'e.g ">0", "=Test*", etc.
    
    With Sheets("RawData")
        .Columns(SearchColumn).AutoFilter Field:=1, Criteria1:=SearchCriteria
        .Range(SearchColumn & "2:" & SearchColumn & .Range(SearchColumn & "2").End(xlDown).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Columns(SearchColumn).AutoFilter
    End With
    Even though UsedRange can be used to find the last row in a sheet, it is only updated when the workbook is saved. So if rows have been added or removed since the last save, this would not be reflected in UsedRange. That is why I used the End(xlDown).Row method.

    The Intersect function here can be simplified to wsB.UsedRange.Columns("C")

    And, I really can't see any purpose for the .Copy in that code. It is copying the entire contents of the used range in column C and pasting them in the second row of column B (assuming column B is all blank cells or all cells with data). But then it doesn't do anything with the copied data. It applies the filter to column C, then deletes all rows in the intersect between the UsedRange and Column C, visible or not.

  14. #14
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Deleting Row in Excel VBA

    I just thought of a problem. Column C is potentially going to contain blanks. That is why you are trying to delete them. But the code I supplied tries to find the last row in column C using the .End(xlDown) method. This will not work if there is a mix of cells with data and blank cells in column C. It needs to check a column that has contiguous data. So we would need to define the Key Column somehow. I had been testing using a criteria other than a blank value.

    StartColumn = "A"
    SearchColumn = "C"
    SearchCriteria = "" 'e.g ">0", "=Test*", etc.
    
    With Sheets("RawData")
        .Columns(SearchColumn).AutoFilter Field:=1, Criteria1:=SearchCriteria
        .Range(StartColumn & "2:" & StartColumn & .Range(SearchColumn & "2").End(xlDown).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Columns(SearchColumn).AutoFilter
    End With

  15. #15
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Deleting Row in Excel VBA

    Quote Originally Posted by Whizbang View Post
    I just thought of a problem. Column C is potentially going to contain blanks. That is why you are trying to delete them. But the code I supplied tries to find the last row in column C using the .End(xlDown) method. This will not work if there is a mix of cells with data and blank cells in column C. It needs to check a column that has contiguous data. So we would need to define the Key Column somehow. I had been testing using a criteria other than a blank value.

    StartColumn = "A"
    SearchColumn = "C"
    SearchCriteria = "" 'e.g ">0", "=Test*", etc.
    
    With Sheets("RawData")
        .Columns(SearchColumn).AutoFilter Field:=1, Criteria1:=SearchCriteria
        .Range(StartColumn & "2:" & StartColumn & .Range(SearchColumn & "2").End(xlDown).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Columns(SearchColumn).AutoFilter
    End With
    Will this work under Option Explicit rules?

  16. #16
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Deleting Row in Excel VBA

    Sure. You just need to declare your variables is all. Option explicit simply forces you to delcare all variables. This helps prevent typos while coding, and therefore reduces bugs.

    So...
    Dim StartColumn as String
    Dim SearchColumn as String
    Dim SearchCriteria as String
    
    StartColumn = "A"
    SearchColumn = "C"
    SearchCriteria = "" 'e.g ">0", "=Test*", etc.
    
    With Sheets("RawData")
        .Columns(SearchColumn).AutoFilter Field:=1, Criteria1:=SearchCriteria
        .Range(StartColumn & "2:" & StartColumn & .Range(StartColumn& "2").End(xlDown).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Columns(SearchColumn).AutoFilter
    End With
    Last edited by Whizbang; 04-06-2012 at 04:08 PM.

  17. #17
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Deleting Row in Excel VBA

    Sorry, been dealing with the courts for the past day. Just one more question, the e.g means what in the formula?

  18. #18
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Deleting Row in Excel VBA

    That is just a comment. Everything past the ' will not be processed in any way. It is just a way to leave notes to anyone who might read the code in the future.

    Here is a good article that talks about basic programming practices: http://www.geekgirls.com/vba_techniques.htm

  19. #19
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Deleting Row in Excel VBA

    Quote Originally Posted by Whizbang View Post
    That is just a comment. Everything past the ' will not be processed in any way. It is just a way to leave notes to anyone who might read the code in the future.

    Here is a good article that talks about basic programming practices: http://www.geekgirls.com/vba_techniques.htm
    The only reason I asked is because after looking at this:

    SearchCriteria = "" 'e.g ">0", "=Test*", etc.
    It should be if I take out the comments:

    SearchCriteria = "#>=0"
    Correct?

  20. #20
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Deleting Row in Excel VBA

    Try it and see. Or highlight the text below to reveal the answer.

    Doing so will make your criteria behave as text. So, unless you have the characters "#>0" in your cells, the table will filter down to no rows. ">0" is the proper syntax to get cells that have a value greater than 0.

  21. #21
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Deleting Row in Excel VBA

    So how would I do it make it work as a number? The field I need deleted the way I have it will work, but I may need it to work as text one time in the future.

    Also I noticed something...

    .Range(StartColumn & "2:"
    The & "2:" part of it, what does that do?
    Last edited by Cyberpawz; 04-10-2012 at 11:31 AM.

  22. #22
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Deleting Row in Excel VBA

    To make it work as a number, simply use ">0". Excel will interpret that as "greater than 0". If you want to force it to look for the text string of ">0" then you would use "=>0" as your criteria.

    For your second question:

    StartColumn = "A"

    The & concatenates two strings together to create a single string.

    So,...

    StartColumn & "2:"

    ...gives you...

    "A2:"

    StartColumn & "2:" & StartColumn & .Range(StartColumn& "2").End(xlDown).Row
    This evaluates to "A2:A100", assuming you have 100 rows of data.

  23. #23
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Deleting Row in Excel VBA

    Quote Originally Posted by Whizbang View Post
    To make it work as a number, simply use ">0". Excel will interpret that as "greater than 0". If you want to force it to look for the text string of ">0" then you would use "=>0" as your criteria.

    For your second question:

    StartColumn = "A"

    The & concatenates two strings together to create a single string.

    So,...

    StartColumn & "2:"

    ...gives you...

    "A2:"

    StartColumn & "2:" & StartColumn & .Range(StartColumn& "2").End(xlDown).Row
    This evaluates to "A2:A100", assuming you have 100 rows of data.
    So If I wanted to start it from the top, I'd take that out entirely? or how would that be written? The reason I'm asking is because where this is working at, all the rows need to be deleted including from the top down so that what is left is what I require to make this work flawlessly.

  24. #24
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Deleting Row in Excel VBA

    Ok, I've just tried running this for the first time, I see this line as red.

        .Range(StartColumn & "6:" & StartColumn & .Range(StartColumn& "6").End(xlDown).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    The Compiler error is:

    Expected: list separator or )

    this is the entire script.

    
    Option Explicit
    
    Sub UpdateList()
        Dim StartColumn As String
        Dim SearchColumn As String
        Dim SearchCriteria As String
    
        StartColumn = "A"
        SearchColumn = "E"
        SearchCriteria = "" 'e.g ">0", "=Test*", etc.
    
        With Sheets("BackOrder")
            .Columns(SearchColumn).AutoFilter Field:=1, Criteria1:=SearchCriteria
            .Range(StartColumn & "6:" & StartColumn & .Range(StartColumn& "6").End(xlDown).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            .Columns(SearchColumn).AutoFilter
        End With
    End Sub
    The Search Criteria is meant to be empty.
    Last edited by Cyberpawz; 04-10-2012 at 12:50 PM.

  25. #25
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Deleting Row in Excel VBA

    Solved it...

    .Range(StartColumn & "6:" & StartColumn & .Range(StartColumn& "6").End(xlDown).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    is there was no space in
    .Range(StartColumn& "6")
    between the Column and &... I put it there, and it works now. thanks for the help.

+ Reply to Thread

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