-1

I have a data sheet that contains data for an apartment complex. I made userforms to add tenants, edit and delete them. however the way I am deleting them it leaves a gap in my data and it becomes very annoying to have to go in and delete all the rows by hand to keep the sheet looking clean.

Sheet1.Select

Dim FndRng As Range

Set FndRng = Sheet1.Columns("C:C").Find(What:=cboName.Value,    LookIn:=xlFormulas, LookAt:=xlPart, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not FndRng Is Nothing Then 'successful find
FndRng.Offset(0, -1).Value = ""
FndRng.Value = ""
FndRng.Offset(0, 1).Value = ""
FndRng.Offset(0, 2).Value = ""
FndRng.Offset(0, 3).Value = ""
FndRng.Offset(0, 4).Value = ""
FndRng.Offset(0, 5).Value = ""
FndRng.Offset(0, 6).Value = ""
FndRng.Offset(0, 7).Value = ""
FndRng.Offset(0, 8).Value = ""
FndRng.Offset(0, 9).Value = ""
FndRng.Offset(0, 10).Value = ""
FndRng.Offset(0, 11).Value = ""

cboName.Value = ""
txtCode.Value = ""
cboGrade.Value = ""
txtPhone.Value = ""
txtDormitory.Value = ""
cboPhase.Value = ""
cboCourse.Value = ""
cboStatus.Value = ""
txtGrad.Value = ""
txtCsd.Value = ""
txtSsn.Value = ""
txtComments.Value = ""
cboSex.Value = ""

Else ' unable to find the value in "Name"
MsgBox "Name not found in Datasheet, Do not change the name while editing the profile.", , "Name not Found"
End If

End Sub

But as you can see that just leaves a big gap in the data. I want to do something like 

fndRng.row.delete

or something that meant that, if you can follow my thought process?

Thank you in advance for any help!!

Mdaox
  • 81
  • 11
  • Possible duplicate of [Delete a row in Excel VBA](https://stackoverflow.com/questions/7851859/delete-a-row-in-excel-vba) – Michał Turczyn Aug 29 '17 at 06:48
  • I had seen your suggested post but as furnished by Mrig, FndRng.EntireRow.Delete is what I was looking for. I knew what I wanted I just haven't used VBA in so long I was a bit foggy. Thank you very much though! – Mdaox Aug 29 '17 at 07:03

1 Answers1

2

Instead of 

fndRng.row.delete

use

FndRng.EntireRow.Delete
Mrig
  • 11,612
  • 2
  • 13
  • 27