Excel VBA: Delete rows if value not equal to a value?

So I've been searching hard to find why my code hasn't been working, but every time I try, I get a result where nothing is changed. Can someone please tell me what I'm missing? Sorry, I'm a total novice but I'm trying.

   Dim Cell As Range



With Sheets(1)

    ' loop column D until last cell with value (not entire column)

    For Each Cell In .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)

        If Cell.Value <> 110 Then

            Rows(Cell.Row).EntireRow.Delete

        End If

    Next Cell

End With

asked Nov 21 '18 at 19:58

El Asado

183

1

Try adding a . to Rows(Cell.Row).EntireRow.Delete

– Brotato
Nov 21 '18 at 20:01

2

2 things. If you're deleting rows, you need to go backwards through the collection. Also, you need to fully qualify Rows(Cell.Row).EntireRow.Delete.

– Comintern
Nov 21 '18 at 20:01

I'm very sorry, but can you explain what qualify means?

– El Asado
Nov 21 '18 at 20:06

1

Cell.EntireRow.Delete would be more direct

– Tim Williams
Nov 21 '18 at 20:07

1

Also: stackoverflow.com/questions/28439376/…

– Tim Williams
Nov 21 '18 at 20:10

|
show 2 more comments

   Dim Cell As Range



With Sheets(1)

    ' loop column D until last cell with value (not entire column)

    For Each Cell In .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)

        If Cell.Value <> 110 Then

            Rows(Cell.Row).EntireRow.Delete

        End If

    Next Cell

End With

asked Nov 21 '18 at 19:58

El Asado

183

1

Try adding a . to Rows(Cell.Row).EntireRow.Delete

– Brotato
Nov 21 '18 at 20:01

2

2 things. If you're deleting rows, you need to go backwards through the collection. Also, you need to fully qualify Rows(Cell.Row).EntireRow.Delete.

– Comintern
Nov 21 '18 at 20:01

I'm very sorry, but can you explain what qualify means?

– El Asado
Nov 21 '18 at 20:06

1

Cell.EntireRow.Delete would be more direct

– Tim Williams
Nov 21 '18 at 20:07

1

Also: stackoverflow.com/questions/28439376/…

– Tim Williams
Nov 21 '18 at 20:10

|
show 2 more comments

   Dim Cell As Range



With Sheets(1)

    ' loop column D until last cell with value (not entire column)

    For Each Cell In .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)

        If Cell.Value <> 110 Then

            Rows(Cell.Row).EntireRow.Delete

        End If

    Next Cell

End With

asked Nov 21 '18 at 19:58

El Asado

183

   Dim Cell As Range



With Sheets(1)

    ' loop column D until last cell with value (not entire column)

    For Each Cell In .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)

        If Cell.Value <> 110 Then

            Rows(Cell.Row).EntireRow.Delete

        End If

    Next Cell

End With

excel vba excel-vba

asked Nov 21 '18 at 19:58

El Asado

183

asked Nov 21 '18 at 19:58

El Asado

183

asked Nov 21 '18 at 19:58

El Asado

183

asked Nov 21 '18 at 19:58

El Asado

183

asked Nov 21 '18 at 19:58

El Asado

183

1

Try adding a . to Rows(Cell.Row).EntireRow.Delete

– Brotato
Nov 21 '18 at 20:01

2

2 things. If you're deleting rows, you need to go backwards through the collection. Also, you need to fully qualify Rows(Cell.Row).EntireRow.Delete.

– Comintern
Nov 21 '18 at 20:01

I'm very sorry, but can you explain what qualify means?

– El Asado
Nov 21 '18 at 20:06

1

Cell.EntireRow.Delete would be more direct

– Tim Williams
Nov 21 '18 at 20:07

1

Also: stackoverflow.com/questions/28439376/…

– Tim Williams
Nov 21 '18 at 20:10

|
show 2 more comments

1

Try adding a . to Rows(Cell.Row).EntireRow.Delete

– Brotato
Nov 21 '18 at 20:01

2

2 things. If you're deleting rows, you need to go backwards through the collection. Also, you need to fully qualify Rows(Cell.Row).EntireRow.Delete.

– Comintern
Nov 21 '18 at 20:01

I'm very sorry, but can you explain what qualify means?

– El Asado
Nov 21 '18 at 20:06

1

Cell.EntireRow.Delete would be more direct

– Tim Williams
Nov 21 '18 at 20:07

1

Also: stackoverflow.com/questions/28439376/…

– Tim Williams
Nov 21 '18 at 20:10

Try adding a . to Rows(Cell.Row).EntireRow.Delete

– Brotato
Nov 21 '18 at 20:01

2 things. If you're deleting rows, you need to go backwards through the collection. Also, you need to fully qualify Rows(Cell.Row).EntireRow.Delete.

– Comintern
Nov 21 '18 at 20:01

I'm very sorry, but can you explain what qualify means?

– El Asado
Nov 21 '18 at 20:06

Cell.EntireRow.Delete would be more direct

– Tim Williams
Nov 21 '18 at 20:07

Also: stackoverflow.com/questions/28439376/…

– Tim Williams
Nov 21 '18 at 20:10

|
show 2 more comments

2 Answers
2

active

oldest

votes

Instead of looping, make use of excels inbuilt functions, its cleaner and more concise.

With Sheets(1).UsedRange

    .AutoFilter Field:=4, Criteria1:="<>110"

    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

    .AutoFilter

End With

if you insist on looping then use the following code:

With Sheets(1).UsedRange

    For lrow = .Rows.Count To 2 Step -1

        If .Cells(lrow, 4).Value <> 110 Then .Rows(lrow).Delete

    Next lrow

End With

edited Nov 26 '18 at 20:24

answered Nov 21 '18 at 20:16

Reafidy

6,16033172

THANK YOU SO MUCH! Yes, I'll research more into the inbuilt functions to get better, but both of these codes worked perfectly, thank you.

– El Asado
Nov 21 '18 at 20:34

@ElAsado, just a heads up. You might need to change "=110" to "<>110" here, as your title and original post suggests you want to delete the rows where column D does not equal 110. But other than that, you're good I think.

– chillin
Nov 21 '18 at 20:48

1

@chillin, thanks code ammended.

– Reafidy
Nov 21 '18 at 20:56

@Reafidy: You probably haven't noticed, the first solution deletes row 1 regardless of the content. Know how to fix it?

– VBasic2008
Nov 22 '18 at 7:08

1

@VBasic2008, Sure, I updated the answer.

– Reafidy
Nov 26 '18 at 1:05

add a comment |

Untested, but maybe something like:

Option explicit



Sub DeleteRows()



With thisworkbook.worksheets(1)

    ' loop column D until last cell with value (not entire column)



Dim lastRow as long

lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row



Dim rowIndex as long

For rowIndex = lastRow to 2 step -1



If .cells(rowIndex, "D").value2 <> 110 then

.cells(rowIndex, "D").entirerow.delete

End if



Next rowIndex



End With



End sub

If you have a lot of rows, you could use union to build a range consisting of all rows to be deleted, then delete them in one go.

answered Nov 21 '18 at 20:26

chillin

1,457134

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53419648%2fexcel-vba-delete-rows-if-value-not-equal-to-a-value%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

Instead of looping, make use of excels inbuilt functions, its cleaner and more concise.

With Sheets(1).UsedRange

    .AutoFilter Field:=4, Criteria1:="<>110"

    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

    .AutoFilter

End With

if you insist on looping then use the following code:

With Sheets(1).UsedRange

    For lrow = .Rows.Count To 2 Step -1

        If .Cells(lrow, 4).Value <> 110 Then .Rows(lrow).Delete

    Next lrow

End With

edited Nov 26 '18 at 20:24

answered Nov 21 '18 at 20:16

Reafidy

6,16033172

THANK YOU SO MUCH! Yes, I'll research more into the inbuilt functions to get better, but both of these codes worked perfectly, thank you.

– El Asado
Nov 21 '18 at 20:34

@ElAsado, just a heads up. You might need to change "=110" to "<>110" here, as your title and original post suggests you want to delete the rows where column D does not equal 110. But other than that, you're good I think.

– chillin
Nov 21 '18 at 20:48

1

@chillin, thanks code ammended.

– Reafidy
Nov 21 '18 at 20:56

@Reafidy: You probably haven't noticed, the first solution deletes row 1 regardless of the content. Know how to fix it?

– VBasic2008
Nov 22 '18 at 7:08

1

@VBasic2008, Sure, I updated the answer.

– Reafidy
Nov 26 '18 at 1:05

add a comment |

Instead of looping, make use of excels inbuilt functions, its cleaner and more concise.

With Sheets(1).UsedRange

    .AutoFilter Field:=4, Criteria1:="<>110"

    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

    .AutoFilter

End With

if you insist on looping then use the following code:

With Sheets(1).UsedRange

    For lrow = .Rows.Count To 2 Step -1

        If .Cells(lrow, 4).Value <> 110 Then .Rows(lrow).Delete

    Next lrow

End With

edited Nov 26 '18 at 20:24

answered Nov 21 '18 at 20:16

Reafidy

6,16033172

THANK YOU SO MUCH! Yes, I'll research more into the inbuilt functions to get better, but both of these codes worked perfectly, thank you.

– El Asado
Nov 21 '18 at 20:34

@ElAsado, just a heads up. You might need to change "=110" to "<>110" here, as your title and original post suggests you want to delete the rows where column D does not equal 110. But other than that, you're good I think.

– chillin
Nov 21 '18 at 20:48

1

@chillin, thanks code ammended.

– Reafidy
Nov 21 '18 at 20:56

@Reafidy: You probably haven't noticed, the first solution deletes row 1 regardless of the content. Know how to fix it?

– VBasic2008
Nov 22 '18 at 7:08

1

@VBasic2008, Sure, I updated the answer.

– Reafidy
Nov 26 '18 at 1:05

add a comment |

Instead of looping, make use of excels inbuilt functions, its cleaner and more concise.

With Sheets(1).UsedRange

    .AutoFilter Field:=4, Criteria1:="<>110"

    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

    .AutoFilter

End With

if you insist on looping then use the following code:

With Sheets(1).UsedRange

    For lrow = .Rows.Count To 2 Step -1

        If .Cells(lrow, 4).Value <> 110 Then .Rows(lrow).Delete

    Next lrow

End With

edited Nov 26 '18 at 20:24

answered Nov 21 '18 at 20:16

Reafidy

6,16033172

Instead of looping, make use of excels inbuilt functions, its cleaner and more concise.

With Sheets(1).UsedRange

    .AutoFilter Field:=4, Criteria1:="<>110"

    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

    .AutoFilter

End With

if you insist on looping then use the following code:

With Sheets(1).UsedRange

    For lrow = .Rows.Count To 2 Step -1

        If .Cells(lrow, 4).Value <> 110 Then .Rows(lrow).Delete

    Next lrow

End With

edited Nov 26 '18 at 20:24

answered Nov 21 '18 at 20:16

Reafidy

6,16033172

edited Nov 26 '18 at 20:24

answered Nov 21 '18 at 20:16

Reafidy

6,16033172

answered Nov 21 '18 at 20:16

Reafidy

6,16033172

answered Nov 21 '18 at 20:16

Reafidy

6,16033172

THANK YOU SO MUCH! Yes, I'll research more into the inbuilt functions to get better, but both of these codes worked perfectly, thank you.

– El Asado
Nov 21 '18 at 20:34

@ElAsado, just a heads up. You might need to change "=110" to "<>110" here, as your title and original post suggests you want to delete the rows where column D does not equal 110. But other than that, you're good I think.

– chillin
Nov 21 '18 at 20:48

1

@chillin, thanks code ammended.

– Reafidy
Nov 21 '18 at 20:56

@Reafidy: You probably haven't noticed, the first solution deletes row 1 regardless of the content. Know how to fix it?

– VBasic2008
Nov 22 '18 at 7:08

1

@VBasic2008, Sure, I updated the answer.

– Reafidy
Nov 26 '18 at 1:05

add a comment |

THANK YOU SO MUCH! Yes, I'll research more into the inbuilt functions to get better, but both of these codes worked perfectly, thank you.

– El Asado
Nov 21 '18 at 20:34

@ElAsado, just a heads up. You might need to change "=110" to "<>110" here, as your title and original post suggests you want to delete the rows where column D does not equal 110. But other than that, you're good I think.

– chillin
Nov 21 '18 at 20:48

1

@chillin, thanks code ammended.

– Reafidy
Nov 21 '18 at 20:56

@Reafidy: You probably haven't noticed, the first solution deletes row 1 regardless of the content. Know how to fix it?

– VBasic2008
Nov 22 '18 at 7:08

1

@VBasic2008, Sure, I updated the answer.

– Reafidy
Nov 26 '18 at 1:05

THANK YOU SO MUCH! Yes, I'll research more into the inbuilt functions to get better, but both of these codes worked perfectly, thank you.

– El Asado
Nov 21 '18 at 20:34

@ElAsado, just a heads up. You might need to change "=110" to "<>110" here, as your title and original post suggests you want to delete the rows where column D does not equal 110. But other than that, you're good I think.

– chillin
Nov 21 '18 at 20:48

@chillin, thanks code ammended.

– Reafidy
Nov 21 '18 at 20:56

@Reafidy: You probably haven't noticed, the first solution deletes row 1 regardless of the content. Know how to fix it?

– VBasic2008
Nov 22 '18 at 7:08

@VBasic2008, Sure, I updated the answer.

– Reafidy
Nov 26 '18 at 1:05

add a comment |

Untested, but maybe something like:

Option explicit



Sub DeleteRows()



With thisworkbook.worksheets(1)

    ' loop column D until last cell with value (not entire column)



Dim lastRow as long

lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row



Dim rowIndex as long

For rowIndex = lastRow to 2 step -1



If .cells(rowIndex, "D").value2 <> 110 then

.cells(rowIndex, "D").entirerow.delete

End if



Next rowIndex



End With



End sub

If you have a lot of rows, you could use union to build a range consisting of all rows to be deleted, then delete them in one go.

answered Nov 21 '18 at 20:26

chillin

1,457134

add a comment |

Untested, but maybe something like:

Option explicit



Sub DeleteRows()



With thisworkbook.worksheets(1)

    ' loop column D until last cell with value (not entire column)



Dim lastRow as long

lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row



Dim rowIndex as long

For rowIndex = lastRow to 2 step -1



If .cells(rowIndex, "D").value2 <> 110 then

.cells(rowIndex, "D").entirerow.delete

End if



Next rowIndex



End With



End sub

If you have a lot of rows, you could use union to build a range consisting of all rows to be deleted, then delete them in one go.

answered Nov 21 '18 at 20:26

chillin

1,457134

add a comment |

Untested, but maybe something like:

Option explicit



Sub DeleteRows()



With thisworkbook.worksheets(1)

    ' loop column D until last cell with value (not entire column)



Dim lastRow as long

lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row



Dim rowIndex as long

For rowIndex = lastRow to 2 step -1



If .cells(rowIndex, "D").value2 <> 110 then

.cells(rowIndex, "D").entirerow.delete

End if



Next rowIndex



End With



End sub

If you have a lot of rows, you could use union to build a range consisting of all rows to be deleted, then delete them in one go.

answered Nov 21 '18 at 20:26

chillin

1,457134

Untested, but maybe something like:

Option explicit



Sub DeleteRows()



With thisworkbook.worksheets(1)

    ' loop column D until last cell with value (not entire column)



Dim lastRow as long

lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row



Dim rowIndex as long

For rowIndex = lastRow to 2 step -1



If .cells(rowIndex, "D").value2 <> 110 then

.cells(rowIndex, "D").entirerow.delete

End if



Next rowIndex



End With



End sub

If you have a lot of rows, you could use union to build a range consisting of all rows to be deleted, then delete them in one go.

answered Nov 21 '18 at 20:26

chillin

1,457134

answered Nov 21 '18 at 20:26

chillin

1,457134

answered Nov 21 '18 at 20:26

chillin

1,457134

answered Nov 21 '18 at 20:26

chillin

1,457134

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu