VBA code to select data till a particular cell value

[RAJESH1960]

Hello Guys

Sub Test()
Sheets("Kotak Bank Book").Select
Cells.Select
Selection.UnMerge
Rows("1:5").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
End Sub

This sheet is sent to me by a client. I need to clean and arrange the sheet to my requirement which I have been doing it manually till now. Since the working steps are the same for every sheet and every time I have decided to try and write a code step by step.
This code will always select the first 5 rows and delete it. I want the rows above the headings to be deleted each time. So, I need the code to select the rows above the column with the “date” heading and delete them. At times the headings may be in the 3rd row or in the 7th row in different data. So I want a code to work accordingly.

TALLY.xlsx
	A	B	C	D	E	F	G	H	I
1	Test Multiple Ledgers
2	Kotak Bank Book
3
4
5	1-Jul-2021 to 2-Jul-2021
6	Date	Particulars				Vch Type	Vch No.	Debit	Credit
7	01-07-2021	Cr	Opening Balance					15000.00
8	01-07-2021	Cr	(as per details)			Receipt		4040.00
9			Sunday						1015.00 Cr
10			Monday						2025.00 Cr
11			Tuesday						1000.50 Cr
12			Round Off					0.50 Dr
13	02-07-2021	Dr	(as per details)			Payment	1004		1001.00
14			January					100.00 Dr
15			February					200.00 Dr
16			March					300.00 Dr
17			April					400.00 Dr
18			Round Off					1.00 Dr
19	02-07-2021	Cr	Cash			Contra	1008	2000.00
20	02-07-2021	Dr	Cash			Contra	1009		25000.00
Bank Ledger

 

[Fluff]

How about

Sub Rajesh()
   Dim Fnd As Range
   
   With Sheets("Kotak Bank Book")
      .UsedRange.UnMerge
      Set Fnd = .Range("A:A").Find("Date", , , xlPart, xlByRows, xlNext, False, , False)
      If Not Fnd Is Nothing And Fnd.Row > 1 Then .Rows("1:" & Fnd.Row - 1).Delete
   End With
End Sub

 

[RAJESH1960]

How about

Sub Rajesh()
   Dim Fnd As Range
  
   With Sheets("Kotak Bank Book")
      .UsedRange.UnMerge
      Set Fnd = .Range("A:A").Find("Date", , , xlPart, xlByRows, xlNext, False, , False)
      If Not Fnd Is Nothing And Fnd.Row > 1 Then .Rows("1:" & Fnd.Row - 1).Delete
   End With
End Sub

Perfect Fluff. Thanks. Please be in touch. I have a long code to write which will help me to finish my one hour work in less than a minute. I will post a new post for the next question as soon as I get stuck once again.?

 

[Fluff]

Glad to help & thanks for the feedback.