SearchRange.Offset, deleting row

[mrkambo]

after performing the below, i want to be able to delete the row on the sheet "database" and shift the data up one row so it isn't empty row there, and for the life of me no matter which way i try it i end up clearing the entire sheet

Any guidance would be appreciated

lastRow = Sheets("Database").Cells(Rows.Count, "J").End(xlUp).Row
Lastrowa = Sheets(ActiveSheet.Name).Cells(Rows.Count, "A").End(xlUp).Row + 1
lastrowb = Sheets("Admin").Cells(Rows.Count, "D").End(xlUp).Row


For x = 1 To lastrowb
ans = Sheets("Admin").Cells(x, 4)
SearchString = ans
Set SearchRange = Sheets("Database").Range("J2:J" & lastRow).Find(SearchString)
If SearchRange Is Nothing Then
Debug.Print ans
Else
Lastrowa = Sheets(ActiveSheet.Name).Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets(ActiveSheet.Name).Range("A" & Lastrowa).Value = SearchRange.Offset(, -2).Value
Sheets(ActiveSheet.Name).Range("B" & Lastrowa).Value = SearchRange.Offset(, -4).Value
Sheets(ActiveSheet.Name).Range("C" & Lastrowa).Value = SearchRange.Offset(, -6).Value
Sheets(ActiveSheet.Name).Range("D" & Lastrowa).Value = SearchRange.Offset(, -5).Value
Sheets(ActiveSheet.Name).Range("E" & Lastrowa).Value = SearchRange.Offset(, 0).Value
Sheets(ActiveSheet.Name).Range("F" & Lastrowa).Value = SearchRange.Offset(, -1).Value
Sheets(ActiveSheet.Name).Range("G" & Lastrowa).Value = SearchRange.Offset(, -9).Value & "   -   " & SearchRange.Offset(, -8)
Sheets(ActiveSheet.Name).Range("H" & Lastrowa).Value = SearchRange.Offset(, 6).Value
Sheets(ActiveSheet.Name).Range("I" & Lastrowa).Value = Format(Now, "DD/MM/YY" & "   -   " & "HH:MM:SS")

 

[Fluff]

Try

LastRow = Sheets("Database").Cells(Rows.Count, "J").End(xlUp).Row
lastrowb = Sheets("Admin").Cells(Rows.Count, "D").End(xlUp).Row


For x = 1 To lastrowb
   SearchString = Sheets("Admin").Cells(x, 4)
   Set SearchRange = Sheets("Database").Range("J2:J" & LastRow).Find(SearchString)
   If SearchRange Is Nothing Then
      Debug.Print ans
   Else
      With SearchRange
         Lastrowa = Cells(Rows.Count, "A").End(xlUp).Row + 1
         Range("A" & Lastrowa).Value = .Offset(, -2).Value
         Range("B" & Lastrowa).Value = .Offset(, -4).Value
         Range("C" & Lastrowa).Value = .Offset(, -6).Value
         Range("D" & Lastrowa).Value = .Offset(, -5).Value
         Range("E" & Lastrowa).Value = .Offset(, 0).Value
         Range("F" & Lastrowa).Value = .Offset(, -1).Value
         Range("G" & Lastrowa).Value = .Offset(, -9).Value & "   -   " & .Offset(, -8)
         Range("H" & Lastrowa).Value = .Offset(, 6).Value
         Range("I" & Lastrowa).Value = Format(Now, "DD/MM/YY" & "   -   " & "HH:MM:SS")
      .EntireRow.Delete
      End With
   End If
Next x

 

[mrkambo]

Try

LastRow = Sheets("Database").Cells(Rows.Count, "J").End(xlUp).Row
lastrowb = Sheets("Admin").Cells(Rows.Count, "D").End(xlUp).Row


For x = 1 To lastrowb
   SearchString = Sheets("Admin").Cells(x, 4)
   Set SearchRange = Sheets("Database").Range("J2:J" & LastRow).Find(SearchString)
   If SearchRange Is Nothing Then
      Debug.Print ans
   Else
      With SearchRange
         Lastrowa = Cells(Rows.Count, "A").End(xlUp).Row + 1
         Range("A" & Lastrowa).Value = .Offset(, -2).Value
         Range("B" & Lastrowa).Value = .Offset(, -4).Value
         Range("C" & Lastrowa).Value = .Offset(, -6).Value
         Range("D" & Lastrowa).Value = .Offset(, -5).Value
         Range("E" & Lastrowa).Value = .Offset(, 0).Value
         Range("F" & Lastrowa).Value = .Offset(, -1).Value
         Range("G" & Lastrowa).Value = .Offset(, -9).Value & "   -   " & .Offset(, -8)
         Range("H" & Lastrowa).Value = .Offset(, 6).Value
         Range("I" & Lastrowa).Value = Format(Now, "DD/MM/YY" & "   -   " & "HH:MM:SS")
      .EntireRow.Delete
      End With
   End If
Next x

This worked a treat, thank you

Can you explain how this method is different from what i originally done, im new to VBA so everything is a learning curve for me

 

[Fluff]

This part of your code

Sheets(ActiveSheet.Name).

is the same as writing

Activesheet.

which is not needed as any unqualified ranges will work on the active sheet.
I also added a With statement, which speeds the code up slightly and (IMO) simplifies it as well. See here https://www.homeandlearn.org/with_end_with.html
And I added this line

.EntireRow.Delete

Which (in conjunction with the With statement) delete the row from your database sheet