Move data to an empty row when deleting an entry

[YingFa]

I have the below code to delete an entry from different sheets. If the entry that is deleted is in between rows, then there will be a blanck space in the databases of the different sheets. Would it be possible to modify my code to move the data 1 row up into the the deleted /empty row in order to avoid having blank spaces/rows in between my data?

Private Sub CommandButton1_Click()

'Update SAR
If Me.ComboBox1.Value = "" Then
MsgBox "SAR Can Not be Blank!", vbExclamation, "SAR"
Exit Sub
End If

SAR = Me.ComboBox1.Value
Sheets("Sheet1").Select

Dim rowSelect As Long
rowSelect = Me.ComboBox1.ListIndex + 6

Cells(rowSelect, 1) = ""
Cells(rowSelect, 2) = ""
Cells(rowSelect, 3) = ""
Cells(rowSelect, 4) = ""
Cells(rowSelect, 5) = ""
Cells(rowSelect, 6) = ""
Cells(rowSelect, 7) = ""
Cells(rowSelect, 8) = ""
Cells(rowSelect, 9) = ""
Cells(rowSelect, 10) = ""
Cells(rowSelect, 11) = ""
Cells(rowSelect, 12) = ""
Cells(rowSelect, 13) = ""
Cells(rowSelect, 14) = ""
Cells(rowSelect, 15) = ""
Cells(rowSelect, 16) = ""
Cells(rowSelect, 17) = ""
Cells(rowSelect, 18) = ""
Cells(rowSelect, 19) = ""
Cells(rowSelect, 20) = ""
Cells(rowSelect, 21) = ""
Cells(rowSelect, 22) = ""
Cells(rowSelect, 23) = ""

Sheets("Sheet2").Select

Cells(rowSelect, 1) = ""
Cells(rowSelect, 2) = ""
Cells(rowSelect, 3) = ""
Cells(rowSelect, 4) = ""
Cells(rowSelect, 5) = ""
Cells(rowSelect, 6) = ""
Cells(rowSelect, 7) = ""
Cells(rowSelect, 8) = ""
Cells(rowSelect, 9) = ""
Cells(rowSelect, 10) = ""
Cells(rowSelect, 11) = ""

Sheets("Sheet3").Select

Cells(rowSelect, 1) = ""
Cells(rowSelect, 2) = ""
Cells(rowSelect, 3) = ""
Cells(rowSelect, 4) = ""
Cells(rowSelect, 5) = ""
Cells(rowSelect, 6) = ""
Cells(rowSelect, 8) = ""
Cells(rowSelect, 9) = ""
Cells(rowSelect, 7) = ""
Cells(rowSelect, 11) = ""
Cells(rowSelect, 12) = ""
Cells(rowSelect, 13) = ""
Cells(rowSelect, 10) = ""
Cells(rowSelect, 15) = ""
Cells(rowSelect, 16) = ""
Cells(rowSelect, 17) = ""
Cells(rowSelect, 14) = ""
Cells(rowSelect, 18) = ""

Sheets("Home").Select
Cells(rowSelect, 1) = ""
Cells(rowSelect, 2) = ""
Cells(rowSelect, 3) = ""
Cells(rowSelect, 4) = ""
Cells(rowSelect, 5) = ""
Cells(rowSelect, 6) = ""
Cells(rowSelect, 7) = ""
Cells(rowSelect, 8) = ""
Cells(rowSelect, 9) = ""
Cells(rowSelect, 10) = ""
Cells(rowSelect, 11) = ""
Cells(rowSelect, 12) = ""
Cells(rowSelect, 13) = ""
Cells(rowSelect, 14) = ""
Cells(rowSelect, 15) = ""
Cells(rowSelect, 16) = ""

'Delete whole folder
  
Dim FSO As Object
Dim MyPath As String
Set FSO = CreateObject("scripting.filesystemobject")
MyPath = "C:\Projects\Project 2\" & SAR

If Right(MyPath, 1) = "\" Then
MyPath = Left(MyPath, Len(MyPath) - 1)
End If

If FSO.FolderExists(MyPath) = False Then
MsgBox MyPath & " was deleted"
Exit Sub
End If

FSO.DeleteFolder MyPath

Unload Me

End Sub

 

[DanteAmor]

Try this

Private Sub CommandButton1_Click()
  'Update SAR
  If Me.ComboBox1.Value = "" Then
    MsgBox "SAR Can Not be Blank!", vbExclamation, "SAR"
    Exit Sub
  End If
 
  SAR = Me.ComboBox1.Value
  Dim rowSelect As Long
  rowSelect = Me.ComboBox1.ListIndex + 6
 
  Sheets("Sheet1").Select
  Rows(rowSelect).Delete
 
  Sheets("Sheet2").Select
  Rows(rowSelect).Delete
 
  Sheets("Sheet3").Select
  Rows(rowSelect).Delete
    
  Sheets("Home").Select
  Rows(rowSelect).Delete
 
  'Delete whole folder
  Dim FSO As Object
  Dim MyPath As String
  Set FSO = CreateObject("scripting.filesystemobject")
  MyPath = "C:\Projects\Project 2\" & SAR
 
  If Right(MyPath, 1) = "\" Then
    MyPath = Left(MyPath, Len(MyPath) - 1)
  End If
 
  If FSO.FolderExists(MyPath) = False Then
    MsgBox MyPath & " was deleted"
    Exit Sub
  End If
  FSO.DeleteFolder MyPath
  Unload Me
End Sub