VBA search specific lines and delete them

[VeKa27]

Hi you all. I need some help in this problem i have..
Imagine a clean excel sheet. In cell A10 i have Text "Stage 1". In cell A20 i have Text "Stage 2". In cell A30 i have Text "Stage 3".
Under every Stage i put some data in the 9 free lines.
Each day i make a reset to delete all data in the 9 free lines to start the day clean.
But now comes the problem..
Sometimes i insert new lines because i had not enough lines. If i reset the next day, i delete the inserted lines again because i only want 9 free lines under every Stage.
Now i'm searching to insert a code to search the cells with the Stages and delete the lines under every stage exept the 9 first ones. I cannot delete the Stages lines themselves.
Below the Stages there is a Table that i also cannot delete or touch.
It is just a question how to delete the new inserted lines and the data in the 9 lines under every Stage.
Thanks a lot to help me with this one

 

[HaHoBe]

Hi Veka27,

code worked in my sample workbook.

Original

MrE_1222736_1614512_vba search specific_221121.xlsm
	A	B	C	D	E	F	G	H
1
2
3
4
5	Stage 1
6	to keep	to keep	to keep	to keep	to keep	to keep	to keep	to keep
7	text
8	text
9	text
10	text
11	text
12	text
13	text	text	text	text	text	text	text	text
14	text
15	text
16	text
17	text
18	text
19	text
20	Stage 2
21	to keep	to keep	to keep	to keep	to keep	to keep	to keep	to keep
22	text
23	text	text	text	text	text	text	text	text
24	text	text	text	text	text	text	text	text
25	text	text	text	text	text	text	text	text
26	text	text	text	text	text	text	text	text
27	text	text	text	text	text	text	text	text
28	text
29	text
30	text
31	text
32	Stage 3
33	to keep	to keep	to keep	to keep	to keep	to keep	to keep	to keep
34	text	text	text	text	text	text	text	text
35	text	text	text	text	text	text	text	text
36	Stage 4
37	to keep	to keep	to keep	to keep	to keep	to keep	to keep	to keep
38	text	text	text	text	text	text	text	text
39	text	text	text	text	text	text	text	text
40	text	text	text	text	text	text	text	text
41	text	text	text	text	text	text	text	text
42	text	text	text	text	text	text	text	text
43	text	text	text	text	text	text	text	text
44	text	text	text	text	text	text	text	text
45	text	text	text	text	text	text	text	text
46	text	text	text	text	text	text	text	text
47	text	text	text	text	text	text	text	text
48	text	text	text	text	text	text	text	text
49	Stage 5
50	to keep	to keep	to keep	to keep	to keep	to keep	to keep	to keep
51	text
52	text
53	text
54	text
55	Stage 6
56	to keep	to keep	to keep	to keep	to keep	to keep	to keep	to keep
57	text	text	text	text	text	text	text	text
58	text	text	text	text	text	text	text	text
59	text	text	text	text	text	text	text	text
60	text	text	text	text	text	text	text	text
61	text	text	text	text	text	text	text	text
62	text	text	text	text	text	text	text	text
63	text	text	text	text	text	text	text	text
64	text	text	text	text	text	text	text	text
65	text	text	text	text	text	text	text	text
66	text	text	text	text	text	text	text	text
67	text	text	text	text	text	text	text	text
Test

After running the code:

MrE_1222736_1614512_vba search specific_221121.xlsm
	A	B	C	D	E	F	G	H
1
2
3
4
5	Stage 1
6	to keep	to keep	to keep	to keep	to keep	to keep	to keep	to keep
7
8
9
10
11	Stage 2
12	to keep	to keep	to keep	to keep	to keep	to keep	to keep	to keep
13
14
15
16
17	Stage 3
18	to keep	to keep	to keep	to keep	to keep	to keep	to keep	to keep
19
20
21
22
23	Stage 4
24	to keep	to keep	to keep	to keep	to keep	to keep	to keep	to keep
25
26
27
28
29	Stage 5
30	to keep	to keep	to keep	to keep	to keep	to keep	to keep	to keep
31
32
33
34
35	Stage 6
36	to keep	to keep	to keep	to keep	to keep	to keep	to keep	to keep
37
38
39
40
41
42
43
44
Test_221124_124657

Slightly modified code used:

Public Sub MrE_1222736_161480C_mod()
' https://www.mrexcel.com/board/threads/vba-search-specific-lines-and-delete-them.1222736/
' Code works on ActiveSheet
Dim lngStart        As Long
Dim var             As Variant

Const cstrSEARCH    As String = "Stage"
Const clngDist      As Long = 6
Const clngStart     As Long = 5
Const cblnHaHoBe    As Boolean = False

lngStart = clngStart + 1
var = lngStart

If cblnHaHoBe Then
  Forum1614516.Copy after:=Sheets(Sheets.Count)
  ActiveSheet.Name = "Test" & Format(Now, "_yymmdd_hhmmss")
End If

Application.ScreenUpdating = False
Do Until Cells(lngStart, "A").Value = ""
  var = Application.Match(cstrSEARCH & "*", Range(Cells(lngStart, "A"), Cells(Rows.Count, "A").End(xlUp)), 0)
  If IsNumeric(var) Then
    If lngStart + var - 1 > lngStart + clngDist Then
      Range(Cells(lngStart + clngDist - 1, "A"), Cells(lngStart + var - 2, "A")).EntireRow.Delete xlShiftUp
      Range(Cells(lngStart + 1, "A"), Cells(lngStart + clngDist - 2, "A")).EntireRow.ClearContents
    ElseIf lngStart + var < lngStart + clngDist Then
      Cells(lngStart + var - 1, "A").Resize(clngDist - var, 1).EntireRow.Insert xlShiftDown
      Range(Cells(lngStart + 1, "A"), Cells(lngStart + clngDist - 2, "A")).EntireRow.ClearContents
    Else
      Range(Cells(lngStart + 1, "A"), Cells(lngStart + clngDist - 2, "A")).EntireRow.ClearContents
    End If
    lngStart = lngStart + clngDist
  Else
    Range(Cells(lngStart + 1, "A"), Cells(Rows.Count, "A").End(xlUp)).EntireRow.ClearContents
    Exit Do
  End If
Loop
Application.ScreenUpdating = True
End Sub

Ciao,
Holger

 

[Legacy 143009]

I have to see your sample data.
Before the code run:

After code runs once

B

 

[VeKa27]

@Flashbond:

 

[Legacy 143009]

Oh, I see.. In real life is your data starting from Column A or B?
Try this. It will search for Stage1, Stage2, etc.. in column B:

Sub myFunction()
  Dim lRow As Integer
  lRow = Cells(Rows.Count, 2).End(xlUp).Row

  For i = 5 To lRow
    If Left(Cells(i, 2).Value, 5) = "Stage" Then
      If Left(Cells(i + 6, 2).Value, 5) <> "Stage" Then
        Cells(i + 6, 2).EntireRow.Delete
        If Cells(i + 6, 2).Value <> "" Then
          i = i - 1
        End If
      End If
    Else
      If Left(Cells(i-1, 2).Value, 5) <> "Stage" Then
        Range(Cells(i, 2), Cells(i, 5)).ClearContents
      End If
    End If
  Next
End Sub

 

[VeKa27]

It is my testing file... please keep searching in column A because in real life it is other data then "stage" in column B (sorry for misunderstanding)

 

[Legacy 143009]

Ok, please try post #24 code in your test file. You'll see it is working.
Then you can use the code in post #17 in real file.
That's all I can do, sorry..

 

[VeKa27]

Ok, no problem... like you say, in test file it works but in real life not..
I will figure it out somehow..
Thanks a lot for your time..

 

[HaHoBe]

Hi VeKa27,

maybe you should not pass pictures but use XL2BB to display the contents of your real data as it seems that the information given to us is different to what you have in your file. Glass marble is broken, Mindreading not available at present - at least from my side .

Holger

 

[I_know_nuffin]

Try the below

Sub deleterows()



' select the correct sheet
'Sheets(Sheets.Count).Activate


Range("A10").Select
If ActiveCell.Offset(10, 0).Value <> "Stage 2" Then
ActiveCell.Offset(10, 0).Select
Do
Rows(ActiveCell.Row).Delete
Loop While ActiveCell.Value <> "Stage 2"
End If

For x = 11 To 19
Rows(x).Clear
Next x
Range("A20").Select
If ActiveCell.Offset(10, 0).Value <> "Stage 3" Then
ActiveCell.Offset(10, 0).Select
Do
Rows(ActiveCell.Row).Delete
Loop While ActiveCell.Value <> "Stage 3"
End If

For x = 21 To 29
Rows(x).Clear
Next x

Range("A30").Select
If ActiveCell.Offset(11, 0).Value <> "" Then
ActiveCell.Offset(10, 0).Select
Do
Rows(ActiveCell.Row).Delete
Loop While ActiveCell.Value <> ""

End If
For x = 31 To 39
Rows(x).Clear
Next x

 

[HaHoBe]

Hi VeKa27,

a little bit of information about the loops and time for each:

Flashbond
Looped for 339 times on 215 rows.
Elapsed time 0,31640625 sec

Code is using 2 loops:

HaHoBe old
Looped for 225 times on 215 rows.
Elapsed time 0,03125 sec

HaHoBe new
Looped for 15 times on 215 rows.
Elapsed time 0,0625 sec

No check for code from I know nuffin: restricted to only 3 stages

Another way would be to load data as array into memory, process and rewrite array to worksheet. But that would require precise information and not a model for test which diesn't suit live data.

Ciao,
Holger