Forum Discussion

jesper1000's avatar
jesper1000
Copper Contributor
Nov 08, 2022

Excel adding and removing data

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

excel
Formulas and Functions

2 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 08, 2022

    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.

     

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Nov 08, 2022

    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)

Resources