Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

VBA Find Value and Delete Row

VBA Find Value and Delete Row
Marc
Hi VBA team,
I have following problem.
I field B3 I have a text/value and this text/value should be searched in column A. When it finds the text/value ( for example A10 ) it should delete the Row.
For any help thanks in advance!
Marc

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: VBA Find Value and Delete Row
30.06.2011 04:11:51
Matthias
Hi
in the example(A1:A30)
File has only one Sheet
Code in Modul
Sub marc()
Dim x&
Application.ScreenUpdating = False
For x = 30 To 1 Step -1
If Cells(x, 1).Value = Cells(3, 2).Value Then Rows(x).Delete
Next
End Sub

https://www.herber.de/bbs/user/75514.xls
Userbild
besser mit StringVariable und Blattreferenz
30.06.2011 04:31:31
Matthias
Hallo
Option Explicit
Sub marc()
Dim x&, strg$
Application.ScreenUpdating = False
With Tabelle1
strg = .Cells(3, 2).Value
For x = 30 To 1 Step -1
If .Cells(x, 1).Value = strg Then .Rows(x).Delete
Next
End With
End Sub

https://www.herber.de/bbs/user/75515.xls
Userbild
Anzeige
Alternative
30.06.2011 08:10:12
Erich
Hi Marc,
the following code may be an alternative.
It will search the text within the whole column A:

Sub marc2()
Dim lngR As Long, strT As String, rngDel As Range
With Sheet1
strT = .Cells(3, 2).Value        ' Text from B3
For lngR = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1
If .Cells(lngR, 1).Value = strT Then
If rngDel Is Nothing Then
Set rngDel = .Cells(lngR, 1)
Else
Set rngDel = Union(rngDel, .Cells(lngR, 1))
End If
End If
Next
End With
If Not rngDel Is Nothing Then rngDel.EntireRow.Delete
End Sub
Your response is welcome! - Greetings from Erich, Kamp-Lintfort
Anzeige
AW: VBA Find Value and Delete Row
01.07.2011 02:21:15
Marc
Hi Erich & Matthias,
perfect. Those solutions are working just fine.
Thanks a lot for your help!
Best Regards
Marc

315 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige