Deleting Row in Excel VBA

[Cyberpawz]

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

[jraj1106]

Hi
Try this:

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

End Sub

[Whizbang]

No need to use the select statement.

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

End Sub

[Cyberpawz]

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?

[Whizbang]

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.

[Cyberpawz]

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?

[Whizbang]

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.

[Cyberpawz]

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.

[Whizbang]

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.

[Cyberpawz]

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

[Cyberpawz]

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

[Whizbang]

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

[Whizbang]

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.

[Whizbang]

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

[Cyberpawz]

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?

[Whizbang]

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

[Cyberpawz]

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

[Whizbang]

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

[Cyberpawz]

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?

[Whizbang]

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.

[Cyberpawz]

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?

[Whizbang]

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.

[Cyberpawz]

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.

[Cyberpawz]

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.

[Cyberpawz]

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.