VBA to delete row based on cell value

[dumbitdown]

I'm trying to get some code to help me delete rows from a data set when a value in column I = 2016, everything I've seen so far just ends up deleting all rows of data, now those that I've tried to specify as having 2016 in column I

There are several hundred rows of data and it gets refreshed with new stuff on a fairly regular basis so there is no defined volume of records in the list that is being used.

Would someone be able to provide me with some code to help with this?

Thanks

 

[dumbitdown]

You may want to take a look at the links I provided, as I think the code provided here does not account for the header row (if there is one), and it will delete that too.

Trying to use them and can't seem to get them working... I'll keep tinkering and see what I can do

Cheers

 

[Joe4]

If you still run into issues, please let us know the following so we can try to recreate the scenario on our side.
- What row does your data start on?
- Is there a header row?
- How many column of data do you have?
- Is the 2016 hard-coded or the result of a formula?
- Is the 2016 numeric, or a number entered in text format (usually indicated if the number is left-justified)?

 

[dumbitdown]

If you still run into issues, please let us know the following so we can try to recreate the scenario on our side.
- What row does your data start on?
- Is there a header row?
- How many column of data do you have?
- Is the 2016 hard-coded or the result of a formula?
- Is the 2016 numeric, or a number entered in text format (usually indicated if the number is left-justified)?

Thanks Joe4, think I've managed to resolve it, I used Macro recorder, however I sorted the data first prior to attempting to delete and that seems to have done the trick

True test will come when I have a refreshed data set to see if it works

Cheers

 

[Rick Rothstein]

Try this:

Sub FilterMini()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "I").End(xlUp).Row
With ActiveSheet.Range("I1:I" & Lastrow)
.AutoFilter Field:=1, Criteria1:="2016", Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).[B][COLOR="#FF0000"]Rows[/COLOR][/B].Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

Shouldn't Rows above be EntireRow instead?

 

[My Aswer Is This]

No. I always just use "Rows" its impossible from what I understand to delete a partial row. We can delete a single cell. I never understand why people use "EntireRow"

Shouldn't Rows above be EntireRow instead?

 

[Rick Rothstein]

No. I always just use "Rows" its impossible from what I understand to delete a partial row. We can delete a single cell. I never understand why people use "EntireRow"

It must be due to the combination of AutoFilter and xlCellTypeVisible why it works because if you use, for example, SpecialCells(xlCellTypeBlanks) on a single column with non-contiguous blank cells scattered about, and use Rows.Delete, you will only get those blank cells deleted and all data from the right moves over one column to fill the deleted cells... only using EntireRow gets the entire row deleted.

 

[My Aswer Is This]

When I use a script like this, It always deletes the entire row also:

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "G").End(xlUp).Row
For i = Lastrow To 1 Step -1
    If Cells(i, "G").Value = "Tom" Then Rows(i).Delete
    Next
Application.ScreenUpdating = True
End Sub

It must be due to the combination of AutoFilter and xlCellTypeVisible why it works because if you use, for example, SpecialCells(xlCellTypeBlanks) on a single column with non-contiguous blank cells scattered about, and use Rows.Delete, you will only get those blank cells deleted and all data from the right moves over one column to fill the deleted cells... only using EntireRow gets the entire row deleted.

 

[Rick Rothstein]

When I use a script like this, It always deletes the entire row also:

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "G").End(xlUp).Row
For i = Lastrow To 1 Step -1
    If Cells(i, "G").Value = "Tom" Then Rows(i).Delete
    Next
Application.ScreenUpdating = True
End Sub

That is different from what I was referring to... here is what I meant. Set this up...

	A	B	C	D
AA1	BB1	CC1
AA2	BB2	CC2
AA3	CC3	CC3
	CC4
	CC5
	CC6
AA8	BB8	CC7
AA13	CC13	CC8
	CC9
AA17	BB17	CC10
	CC11
AA20		CC12
	CC13
	CC14
AA26	BB21	CC15

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]

</tbody>

Then run this macro on it (note my use of Rows)...

Sub Demo()
  Range("A1:A15").SpecialCells(xlCellTypeBlanks).[B][COLOR="#FF0000"]Rows[/COLOR][/B].Delete
End Sub

When you execute this code, the entire row will not be deleted, only the cells in Column A are deleted and the data in Columns B and C move over to fill the deleted cells... only by using EntireRow will the entire row be deleted. The reason I thought you needed to use EntireRow in your original code is because your deletion line is the same as in the above code except you used xlCellTypeVisible instead of xlCellTypeBlanks and you applied your code against filted data... my guess is that filtered data coupled with SpecialCells(xlCellTypeVisible) makes your use of Rows work..

 

[My Aswer Is This]

O yes. I see. Thanks for that info.

That is different from what I was referring to... here is what I meant. Set this up...

	A	B	C	D
AA1	BB1	CC1
AA2	BB2	CC2
AA3	CC3	CC3
	CC4
	CC5
	CC6
AA8	BB8	CC7
AA13	CC13	CC8
	CC9
AA17	BB17	CC10
	CC11
AA20		CC12
	CC13
	CC14
AA26	BB21	CC15

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]

[TD="align: right"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]

[TD="align: right"]2
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]

[TD="align: right"]3
[/TD]

[TD="bgcolor: #CACACA, align: center"]7
[/TD]

[TD="bgcolor: #CACACA, align: center"]8
[/TD]

[TD="bgcolor: #CACACA, align: center"]9
[/TD]

[TD="align: right"]4
[/TD]

[TD="bgcolor: #CACACA, align: center"]10
[/TD]

[TD="bgcolor: #CACACA, align: center"]11
[/TD]

[TD="align: right"]5
[/TD]

[TD="bgcolor: #CACACA, align: center"]12
[/TD]

[TD="bgcolor: #CACACA, align: center"]13
[/TD]

[TD="align: right"]6
[/TD]

[TD="bgcolor: #CACACA, align: center"]14
[/TD]

[TD="align: right"]7
[/TD]

[TD="bgcolor: #CACACA, align: center"]15
[/TD]

[TD="bgcolor: #CACACA, align: center"]16
[/TD]

</tbody>

Then run this macro on it (note my use of Rows)...

Sub Demo()
  Range("A1:A15").SpecialCells(xlCellTypeBlanks).[B][COLOR=#ff0000]Rows[/COLOR][/B].Delete
End Sub

When you execute this code, the entire row will not be deleted, only the cells in Column A are deleted and the data in Columns B and C move over to fill the deleted cells... only by using EntireRow will the entire row be deleted. The reason I thought you needed to use EntireRow in your original code is because your deletion line is the same as in the above code except you used xlCellTypeVisible instead of xlCellTypeBlanks and you applied your code against filted data... my guess is that filtered data coupled with SpecialCells(xlCellTypeVisible) makes your use of Rows work..