Deleting cells with specific values 1

[canfresca]

I'm trying to write a macro that will look at a cell value, and if the value is -1, I want it to delete that cell plus the cell to the left of it. (The two data colums go together)

1)How to I make the If statement saying "If cellval = -1"
2)How do I select that cell (and the one left of it) to delete it?

I want it to search through the entire excel spreadsheet for these negative values. Here is what I have. Can someone tell me what's wrong?

Sub Delete()
Dim Row As Integer
Dim col As Integer
Dim cellname As Integer

col = 2
Row = 2

For col = 2 To 150
For Row = 2 To 340
cellname = Cells(Row, col).Value
If cellname = -1 Then
Range(Cells(Row, col)).Select
Selection.Delete Shift:=xlUp
Range(Cells(Row, col - 1)).Select
Selection.Delete Shift:=xlUp
End If
Row = Row + 1
Next
col = col + 3
Next
End Sub

Thanks!

 

[AnotherHiggins]

Do you want to delete the entire row? If not, do you want to move other cells left or up?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

I just took a look at your code, and I have another question. What does your data look like? Could you please provide a small sample of your dataset?

Your code seeks to loop through columns B through ET - 150 columns wide. Do you actually have 150 columns?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[canfresca]

So for example:

0.89 -1
0.895 -1
0.9 -1
0.905 1.146
0.91 1.148
0.915 1.1397
0.92 1.139
0.925 1.1545
0.93 1.1229

I want to delete the cells in column 1 and 2 for the first three because they have -1 values. And yes there are over 300 rows and about 100 columns of data

 

[canfresca]

And no, I don't want to delete the entire row, just the -1 value and the value left of it and then shift all other values up.

 

[AnotherHiggins]

Give this a try:

Sub DeleteCells()
For Each cell In Range([B2], Range("B" & ActiveSheet.Rows.Count).End(xlUp))
    If cell.Value = -1 Then
        Range(cell, cell.Offset(, -1)).Delete shift:=xlUp
    End If
Next cell
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[canfresca]

Thanks so much!

 

[canfresca]

One more thing. This code works only in column B, is there anyway to get it to loop through more columns? (My sheet goes up to DA)

 

[AnotherHiggins]

Actually, I fell into a trap that I've warned others about in the past: If you have two -1's in a row, my code will skip the second one. Starting from the bottom and working up is one way to avoid this:

Sub DeleteCells()
For i = Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row To 2 Step -1
    If Range("B" & i).Value = -1 Then
        Range(Range("B" & i), Range("A" & i)).Delete shift:=xlUp
    End If
Next i
End Sub

As for dealing with the other columns.... I'm probably being dense, but I'm still not sure what exactly you want to happen.

What would you want to end up with if your data started out like this?:
[tt]
Header1 Header2 Header3 Header4
X -1 1 0
X 1 -1 -1
X -1 -1 -1
X 1 -1 -1
X X X X
X X X X
X X X X[/tt]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[canfresca]

Basically I want to treat every 3 cols independently

Col 1 Col 2 Col 3 Col 4 Col 5 Col 6
x -1 x x
x x x -1
x x x x
x -1 x x
x x x x
x -1 x -1

col 1,4 (and other multiples) all have positive vals
col 2,5... have good vals and then -1 that need to be rejected (as well as the corresponding value in the column before)
col 3,6... are all blank to seperate the pairs of columns.

I figured it would be easiest to just loop through each column looking for negative values to delete.

Does this make it clearer?

Thanks.

 

[AnotherHiggins]

OK - I thought I must have been missing something! If you had contiguous data throughout the entire sheet and evaluated every column, that just wouldn't make sense!

Here's a little something that should work for you:

Sub DeleteCells_2()
For i = 2 To Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column Step 3
    For j = Cells(ActiveSheet.Rows.Count, i).End(xlUp).Row To 2 Step -1
        If Cells(j, i).Value = -1 Then
            Range(Cells(j, i), Cells(j, i - 1)).Delete shift:=xlUp
        End If
    Next j
Next i
End Sub

Notice the "Step 3" in the first line. That says to increment from 2 to 5 to 8, etc.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[canfresca]

Great,

That works perfectly. And thanks for the Step 3 info. I was try to add three before and it wasn't working.

Thanks.

 

[AnotherHiggins]

Happy to help. And thanks for the Star.

I see that this is your first post on Tek-Tips. When you have time, look around a bit. This site is a great resource and I have learned tons by following other's questions and reading through the generous explanations of some of the gurus around here.

Have a great weekend.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.