• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Delete values on row based on cell value (not entire row really)

C

cacos

Member
Hi!


I'm having some trouble with this code. I want vba to delete every row where a cell meets a certain criteria (let's say "House"). But not the entire row, only between columns "A" and "N" for example.


This is what I managed to conquer so far, but that only deletes the specific cell:

[pre]
Code: 
Sub Test()
For Each Cell In Sheet1.[A1:N30000]

If Cell.Value = "HOUSE" Then Cell.ClearContents

Next Cell

End Sub
[/pre]

Thanks!!
 
Hi, cacos!


Syntax error. Try this:

-----

[pre]
Code: 
Option Explicit

Sub Test()
Dim Cell As Range
For Each Cell In Worksheets("Sheet1").[A1:N30000]
If Cell.Value = "HOUSE" Then Cell.ClearContents
Next Cell
End Sub
[/pre]
-----


Actually first line "Option Explicit" and "Dim Cell As Range" statement are optional, but it's a good practice to use them so as to prevent unwanted errors.


Technically speaking, you're not deleting cells, you're only clearing its contents, i.e. erasing values or formulas. Delete operations imply shifting left or up the remaining cells in row or column.


Regards!
 
Thanks! you are right, Im clearing contents.

So how can I get it to delete the rows where that cell is, yet only between columns A and N?


Deleting/clearing contents any will do actually.
 
Good day cacos


You can not delete part of a row or part of a column all you can do as SirJB7 pointed out to you is clear the contents of the selected cells. If you want to delete rows or columns you must delet ALL of the row or column. SirJB7 has show you the code the clear contents, if it is more then one cell adjust the range to suit.
 
Hi ,


I am not sure I have understood you correctly , but try this :

[pre]
Code: 
Sub Test()
Dim Cell As Range
For Each Rw In Worksheets("Sheet2").[A1:N30000].Rows
On Error Resume Next
If IsNumeric(Application.Match("HOUSE", Rw, 0)) Then Rw.ClearContents
On Error GoTo 0
Next
End Sub
[/pre]
Narayan
 
You can skip looping through ranges.

[pre]
Code: 
Public Sub RemData()
Sheets("Sheet2").Range("A1:N30000").Replace "*House*", vbNullString, xlWhole, xlByRows, False
End Sub
[/pre]
 
Thanks!


Narayank, I've tried to adapt your code to delete instead of clearing contents, and it deletes some rows but not all.


Is there a way of making it delete instead of clearing contents?


If it's not possible to delete between ranges of columns, how could I get it to delete the whole row?


Thanks again.
 
Hi, cacos!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/VBA%20-%20Delete%20values%20on%20row%20based%20on%20cell%20value%20%28not%20entire%20row%20really%29%20%28for%20cacos%20at%20chandoo.org%29.xlsm


I copied this formula to range A1:p50:

=ELEGIR(ALEATORIO.ENTRE(1;10);"World";"Continent";"Country";"Province";"District";"City";"Neighbourhood";"Apple";"Street";"House") -----> in english: =CHOOSE(RANDBETWEEN(1,10),"World","Continent","Country","Province","District","City","Neighbourhood","Apple","Street","House")


Then I run this code:

-----

[pre]
Code: 
Option Explicit

Sub Test()
Dim Cell As Range
For Each Cell In Worksheets("Hoja1").[A1:N50]
If Cell.Value = "House" Then Cell.Delete xlUp
Next Cell
End Sub
[/pre]
-----


Is that what you were looking for? I only should advise you that it might take a moment or two for your stated range, try it.


Regards!
 
Hi, cacos!


Updated. Download again the uploaded file from same previous link. The code was adjusted to this:

-----

[pre]
Code: 
Option Explicit

Sub Test()
Dim Cell As Range
For Each Cell In Worksheets("Hoja1").[A1:N50]
If Cell.Value = "House" Then Cell.EntireRow.Delete xlUp
Next Cell
End Sub
[/pre]
-----


Regards!
 
I used the new code and I don't know why it skips some rows.


I mean, it deletes most of the rows where "House" appears, but it also leaves a few of them.


Do you know why this could be happening?


Thanks!
 
Hi, cacos!


In my uploaded sample file or in your actual file? If in mine, it's because I uses a random formula (CHOOSE & RANDBETWEEN) as I told you upwards:

http://chandoo.org/forums/topic/vba-delete-values-on-row-based-on-cell-value-not-entire-row-really#post-98592

for easiness in filling the range for the test.


You can simply take the original file, select the yellow range and paste values over itself. Then run the macro and check if works fine. Take care of leaving any row without "House" so as verify if it remains after the process. If I were you, I'd make a copy of my actual data and I'd run the macro on it.


Otherwise you yet know the routine (file, upload, ...).


Regards!
 
Hi, cacos!


I forgot to mention that RANDBETWEEN is a volatile function, that's to say it gets recalculated each time any change in the worksheet happens, so after any delete all previously cells skipped because before they hadn't the key value, are recalculated and a new random value is assigned, with a 10% (1 in 10) or probability to receive a key value.


Copying and pasting values avoids this recalculation.


Regards!


PS: If you want to see how volatile functions work, press F9 repeatedly and see what happens to the cells. Do this prior copy & pasting values.
 
Yupppp noticed it was a randbetween so I pasted values.


I tried with that same set of data you uploaded as well as a new one and it still leaves a few ocurrences where the criteria matches undeleted.


Check in that same file and you'll see that some rows with "HOUSE" remain (not all, but a few remain).
 
Hi, cacos!

My mistake, I apologize. When deleting cells or row from a range you should start from the end to the beginning so as to don't skip any cell/row because of the change in the range original address.

I converted the values to constants, added 2 columns Q & R to test if the row should be deleted or kept, and in 2nd sheet I placed 2 counters for them: one with formula that will be changed after running the macro, and other as constants to place a witness role.

Please download again the updated file from same previous link.

Regards!
 
Hi, cacos!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top