Excel adding and removing data

Occasional Visitor

In my excel sheet I have one tab (Tab A) containing vast amounts of serial numbers, and another tab (Tab B) it which i want to make a sort of shipment list containing extracts from tab A.

 

I am trying to make it so when I add a serial number in tab B it automatically removes the corresponding cell containing the serial number in the first A without me, manually having to find it in tab A and remove it. 

 

Is this possible ? Can anyone please advise?

 

Thanks!

/Jesper

2 Replies

@jesper1000 

try it with "=MATCH" or "=VLOOKUP" .

 

or with VBA if it is not Excel Online.

Here is a small example, you can adapt it in your needs.

 

Sub_delete_rows()

Application.DisplayAlerts = False
With ActiveSheet
.Range("A1").AutoFilter Field:=3, Criteria1:="x"
.Rows(1).Hidden = True
.UsedRange.SpecialCells(xlCellTypeVisible).Delete
.Rows(1).Hidden = False
.AutoFilterMode = False
End With
Application.DisplayAlerts = True

end sub

 

 

Hope I was able to help you with this info.

 

NikolinoDE

I know I don't know anything (Socrates)

@jesper1000 

Sub Serial_number()

Dim i As Long
Dim j As Long
Dim k As Long
Dim searchstring As String

i = Sheets("Tabelle1").Cells(Rows.Count, "A").End(xlUp).Row
k = Application.WorksheetFunction.Find(" ", Sheets("Tabelle2").Cells(2, 1))
searchstring = Left(Sheets("Tabelle2").Cells(2, 1), k - 1) & " "

For j = 2 To i
If InStr(Sheets("Tabelle1").Cells(j, "A").Value, searchstring) > 0 Then
        Sheets("Tabelle1").Cells(j, "A").Value = Sheets("Tabelle2").Cells(2, 1).Value
Exit Sub
End If
Next j

End Sub

An alternative could be these lines of code. As an unambiguous identifier i chose column names followed by a space e.g. "A " or "BN ". In sheet "Tabelle2" you can enter a new serial number in cell A2 e.g. "DA 11111111" and then click the button in cell D2. The entry from cell A2 is then entered in cell A106 in sheet1 "Tabelle1" in this example.

enter new serial number.JPG

serial number.JPG