Inserting missing rows and #s

[jimmyrvw]

This is probably a simple bit of coding, but I'm new to VBA and am totally stumped. I work at a homeless shelter and have to make and format rosters numerous times during the day. The program I use for record keeping lets me save records in Excel 2007. If there is no client assigned to a bed the tracking program deletes the whole row, not just the clients name but the bed # also. In this example the macro would have to insert rows and add bed #s for Bed 11, 13 & 20. There are three dorms, one with 26 beds, one at 55 beds and the last has 65 beds. Right now I have to manually do the insertions myself to get the macros used to format printing to work. Please any help will be appreciated. The Name and Bed headers are separate columns




Name Bed
Client a 1
Client x 2
Client l 3
Client b 4
Client q 5
Client w 6
Cliente 7
Client r 8
Client t 9
Client y 10
Client u 12
Client i 14
Client m 15
Client n 16
Client b 17
Client h 18
Client d 19
Client f 21
Client s 22
Client j 23

 

[texasalynn]

try this macro

Sub MissingBed()
Dim LR As Long, i As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If Cells(i - 1, 2) <> Cells(i, 2) - 1 Then
    Rows(i).EntireRow.Insert
    Cells(i, 2) = Cells(i - 1, 2) + 1
End If
Next i
MsgBox "Done"
End Sub