• 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.

Delete all rows above the cell that contain specific string

freshtomm

Member
Hello,

i need a vba code that will delete all rows above the cell that contains specific string, is that possible? I got this one, but it will only delete two rows above.


Code:
Dim x
For x = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row To ActiveCell.Row Step -1
  If Cells(x, 1) = "Slovakia" Then 'If we find this text
  Cells(x - 1, 1).EntireRow.delete  'Delete the row above it
  Cells(x - 2, 1).EntireRow.delete  'Delete the row 2 rows above it
  x = x - 2
  'Delete blank rows
  ElseIf Cells(x, 1) = vbNullString Then Cells(x, 1).EntireRow.delete
  'Optional delete rows that contain "File looks like ..."
  'ElseIf Cells(x, 1) = "File looks like it is not encrypted. Skipping ..." Then Cells(x, 1).EntireRow.Delete
  'ElseIf Cells(x, 1) = "File could not be decrypted properly. Skipping ..." Then Cells(x, 1).EntireRow.Delete
  End If
Next x

Thanks.

Tomas
 
Try this code:-
Code:
Sub DeletRowsAbove()

Dim last As Long
Dim x As Long

With ActiveSheet
    last = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
For x = last To 2 Step -1
    If Cells(x, 1).Value = "Slovakia" Then
   
        Cells(x - 1, 1).EntireRow.Delete
    End If
Next x
End With

End Sub
 
1) Activate Any cell from ANY COLUMN which You want to use
2) Run Macro
2) Apply needed 'specific string', default 'specific string' is activecell's value
3) It really deletes ALL rows above 'specific string' no matter how many times it will be there.
4) Of course You have taken backup before run this!
Code:
Sub DeleteRowsAbove()
    On Error Resume Next
    y_min = 1
    y_max = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
    KeyW = InputBox("Word to find: ", "Delete All Rows Above", ActiveCell.Value)
    Application.ScreenUpdating = False
    If KeyW <> Empty Then
        Do
            Err.Clear
            y = WorksheetFunction.Match(KeyW, ActiveSheet.Range("A" & y_min & " :A" & y_max), 0)
            If Err.Number = 0 And y > 1 Then Rows("1:" & y - 1).Delete
            y_min = 1
            If y = 1 Then y_min = 2
            y_max = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
        Loop Until Err.Number <> 0 Or y_max = 1
    End If
End Sub
 
Dear Sirs @vletm

Due to wildcard operator "*" not work with your post no 3's code

How to use your post no 3's code with use of "like" operator (without Match function),

Regards,
Chirag Raval
 
Chirag R Raval
Your original 'hope' was
i need a vba code that will delete all rows above the cell that contains specific string
hmm... Is Your specific string * ?
In some cases
* would make result that it would delete everything...
if that 'specific string' is alone
*.
Could You send a sample file to test changed case?
... with sample 'specific string' combination.
Is there
*text*text*text* or maybe only text* or *text?
After that, I could check this.
 
Works on the active sheet:
Code:
Sub blah()
On Error Resume Next
Rows("1:" & Columns(1).Find("Slovakia", LookIn:=xlFormulas, lookat:=xlWhole, Searchdirection:=xlPrevious, Searchformat:=False).Row - 1).Delete
End Sub
 
Back
Top