I am working on some code that imports data from an external source; the issue however is that the header row sometimes contains blank rows above it. Since these rows are unnecessary, I would like to delete all rows above the header row.
Currently, I have the below code looking for a word in the header row (the word "organization") and deleting the row above. Ideally, I would like to make the code more robust as the amount of rows above the header row varies from time to time. If I can somehow get the code to delete all rows above the header, I would be more than satisfied.
Option Explicit
Dim wks As Worksheet
Dim strSearch As String
Dim lookUp As Range
Private Sub cmdRefresh_Click()
Set wks = Sheets("Data")
wks.Activate
'Delete rows (if any) above header row
strSearch = "Organization"
Set lookUp = wks.Cells.Find(what:=strSearch, lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False)
lookUp.Select
If lookUp.Row <> 1 Then
wks.Range(lookUp, lookUp.Offset(-1)).EntireRow.Delete
End If
End Sub
Any help would be greatly appreciated.
