PDA

View Full Version : [SOLVED] Macro eliminates row and fix alignment



osevero
10-13-2013, 12:17 AM
Hi again,

I have a database that receives data and nobody can change. To delete the data I use a macro where I write the number of the column ID and it deletes the row (can see the code below). And what I want it's when a row is removed, the ID numbers automatically readjust to always stay with the sequence 1,2,3,4,5,6,7,8... You can see this xlsx: 10696 Does anyone know how to do?



Private Sub CommandButton1_Click()


ThisWorkbook.Worksheets("Plan1").Activate


Range("B7").Select
While ActiveCell <> ""
If TextBox1.Text = ActiveCell Then
While ActiveCell = TextBox1.Text
ActiveCell.EntireRow.Delete


Wend
End If
ActiveCell.Offset(1, 0).Activate
Wend


End Sub


Cheers!
: pray2:

nilem
10-13-2013, 02:45 AM
Hi Osevero,
maybe

Private Sub CommandButton1_Click()
Dim r As Range
With Sheets("Plan1")
Set r = .Columns(2).Find(What:=TextBox1.Text, lookat:=xlWhole)
If Not r Is Nothing Then
r.EntireRow.Delete
With .Range("B7", Cells(Rows.Count, 2).End(xlUp))
.FormulaR1C1 = "=ROW(RC[-2])-6"
.Value = .Value
End With
End If
End With
End Sub

osevero
10-17-2013, 07:39 AM
Thanks nilem! :bow: