• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Code to delete row and appear on next sheet

P

Pasadu

Member
I want a code whereby a when the word LOADED appears on one sheet (Shipment), that whole row will be deleted, and all the details moved onto another sheet tab (Loading Status).

Thank you
 
Try the following. It searches COL A for the term LOADED.

Code: 
Sub MoveMyrow()

Dim Sh1 As Worksheet, sh2 As Worksheet
Dim lrsh1 As Long, lrsh2 As Long
Dim mRng As Range
Dim x As Integer

Set Sh1 = ThisWorkbook.Sheets("Shipment")
Set sh2 = ThisWorkbook.Sheets("Loading Status")

lrsh1 = Sh1.Cells(Rows.Count, 1).End(xlUp).Row

lrsh2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row

Set mRng = Sh1.Range("A2:A" & lrsh1)

For x = 2 To mRng.Count
    If CStr(mRng(x).Value) = "Loaded" Then
    
        mRng(x).EntireRow.Copy Destination:=sh2.Range("A2:A" & lrsh2)
        mRng(x).EntireRow.Delete
        If CStr(mRng(x).Value) = "LOADED" Then
            x = x - 1
        End If
            
    End If
Next

End Sub
 
Logit said:
Try the following. It searches COL A for the term LOADED.

Code: 
Sub MoveMyrow()

Dim Sh1 As Worksheet, sh2 As Worksheet
Dim lrsh1 As Long, lrsh2 As Long
Dim mRng As Range
Dim x As Integer

Set Sh1 = ThisWorkbook.Sheets("Shipment")
Set sh2 = ThisWorkbook.Sheets("Loading Status")

lrsh1 = Sh1.Cells(Rows.Count, 1).End(xlUp).Row

lrsh2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row

Set mRng = Sh1.Range("A2:A" & lrsh1)

For x = 2 To mRng.Count
    If CStr(mRng(x).Value) = "Loaded" Then
   
        mRng(x).EntireRow.Copy Destination:=sh2.Range("A2:A" & lrsh2)
        mRng(x).EntireRow.Delete
        If CStr(mRng(x).Value) = "LOADED" Then
            x = x - 1
        End If
           
    End If
Next

End Sub
Click to expand...
Thank you but it did not work. Please find file attached
 

Attachments

  • Moving and Deleting Row.xlsx
    11.3 KB · Views: 3
This works here :'

Code: 
Sub MoveMyrow()

Dim Sh1 As Worksheet, sh2 As Worksheet
Dim lrsh1 As Long, lrsh2 As Long
Dim mRng As Range
Dim x As Integer

Set Sh1 = ThisWorkbook.Sheets("SHIPMENT")
Set sh2 = ThisWorkbook.Sheets("LOADING STATUS")

lrsh1 = Sh1.Cells(Rows.Count, 1).End(xlUp).Row

lrsh2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row

Set mRng = Sh1.Range("A2:A" & lrsh1)

For x = 2 To mRng.Count
    If CStr(mRng(x).Value) = "LOADED" Then
  
        mRng(x).EntireRow.Copy Destination:=sh2.Range("A2:A" & lrsh2).Offset(1, 0)
        mRng(x).EntireRow.Delete
        If CStr(mRng(x).Value) = "LOADED" Then
            x = x + 1
        End If
          
    End If
Next

End Sub
 
Logit said:
This works here :'

Code: 
Sub MoveMyrow()

Dim Sh1 As Worksheet, sh2 As Worksheet
Dim lrsh1 As Long, lrsh2 As Long
Dim mRng As Range
Dim x As Integer

Set Sh1 = ThisWorkbook.Sheets("SHIPMENT")
Set sh2 = ThisWorkbook.Sheets("LOADING STATUS")

lrsh1 = Sh1.Cells(Rows.Count, 1).End(xlUp).Row

lrsh2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row

Set mRng = Sh1.Range("A2:A" & lrsh1)

For x = 2 To mRng.Count
    If CStr(mRng(x).Value) = "LOADED" Then
 
        mRng(x).EntireRow.Copy Destination:=sh2.Range("A2:A" & lrsh2).Offset(1, 0)
        mRng(x).EntireRow.Delete
        If CStr(mRng(x).Value) = "LOADED" Then
            x = x + 1
        End If
         
    End If
Next

End Sub
Click to expand...
I appreciate your support, but it isnt really working as it should. Somehow it copies onto the other sheet but it doesnt delete the row. Also, it copies partially, it isn't instant
 
Not certain what to advise. The macro does work here as intended.
 

Attachments

  • Moving and Deleting Row.xlsb
    17.3 KB · Views: 2
Pasadu
Verify Your written steps:
#1 Loaded
#2 Delete
#3 Move
above means:
#1 if cell A4 get 'Loaded'
#2 delete that row 4 - which have 100E | 7 | 40FT | PAPER
#3 move row, which has 600U | 8 | 20FT | PAPER to LOADING STATUS-sheet
... above means that row will be empty in SHIPMENT-sheet.
OR
something like below:
#1 if cell A4 get 'Loaded'
#2 move row 4 - which have Loaded | 100E | 7 | 40FT | PAPER to LOADING STATUS-sheet
... above means that row will be empty in SHIPMENT-sheet.

How do that 'Loaded' appears in STATUS-column?
... do someone write it?
... is there eg link to somewhere?
 
vletm said:
Pasadu
Verify Your written steps:
#1 Loaded
#2 Delete
#3 Move
above means:
#1 if cell A4 get 'Loaded'
#2 delete that row 4 - which have 100E | 7 | 40FT | PAPER
#3 move row, which has 600U | 8 | 20FT | PAPER to LOADING STATUS-sheet
... above means that row will be empty in SHIPMENT-sheet.
OR
something like below:
#1 if cell A4 get 'Loaded'
#2 move row 4 - which have Loaded | 100E | 7 | 40FT | PAPER to LOADING STATUS-sheet
... above means that row will be empty in SHIPMENT-sheet.

How do that 'Loaded' appears in STATUS-column?
... do someone write it?
... is there eg link to somewhere?
Click to expand...
Everything is typed, it is manual.
 
vletm said:
Pasadu
Verify Your written steps:
#1 Loaded
#2 Delete
#3 Move
above means:
#1 if cell A4 get 'Loaded'
#2 delete that row 4 - which have 100E | 7 | 40FT | PAPER
#3 move row, which has 600U | 8 | 20FT | PAPER to LOADING STATUS-sheet
... above means that row will be empty in SHIPMENT-sheet.
OR
something like below:
#1 if cell A4 get 'Loaded'
#2 move row 4 - which have Loaded | 100E | 7 | 40FT | PAPER to LOADING STATUS-sheet
... above means that row will be empty in SHIPMENT-sheet.

How do that 'Loaded' appears in STATUS-column?
... do someone write it?
... is there eg link to somewhere?
Click to expand...
This is whati need please:
#1 if cell A4 get 'Loaded'
#2 delete that row 4 - which have 100E | 7 | 40FT | PAPER
#3 move row, which has 600U | 8 | 20FT | PAPER to LOADING STATUS-sheet
... above means that row will be empty in SHIPMENT-sheet.
 
vletm said:
Pasadu
SHIPMENT-sheet has Your verified delete row and appear on next sheet-sample.
LOADING STATUS-sheet has Your verified delete entire row-sample.
(( https://chandoo.org/forum/threads/code-to-delete-entire-row.48215/ ))
Have a backups.
Click to expand...
Thank you sir, :), the row deletes perfectly from the Shipment Sheet....But it does not necessarily appear on the Loading Status sheet. All the contents on that deleted row, should all appear in the "loading status sheet". Thank You
 
vletm:

He has now experienced an issue with your example and mine. I suggest there is something going on with his installed copy of EXCEL.
 
Please explain ... both my version and vletm's version successfully delete the information from the first sheet but fail to always paste as expected to the second sheet.

How is the above satisfactory to your needs ? On your end, you only have half a solution. On our ends, there is a complete solution.

How can you be pleased ?

Help me understand.
 
Logit said:
Please explain ... both my version and vletm's version successfully delete the information from the first sheet but fail to always paste as expected to the second sheet.

How is the above satisfactory to your needs ? On your end, you only have half a solution. On our ends, there is a complete solution.

How can you be pleased ?

Help me understand.
Click to expand...
Yes you are right. The row deletes but the other sheet isn't updated successfully. With Vlet's file, the other sheet updates but not perfectly even though it updates.

I'm not overly pleased but I have to appreciate your support. At least I have a code that I could use to delete rows

I will appreciate it if I get the sheet I want, the way I want it. And that's whereby when LOADED appears in the SHIPMENT SHEET TAB, the whole row should be deleted and the information that has been deleted ought to be transferred to the LOADING STATUS SHEET tab.

Thank you.
 
If you dont have a workbook yet ... how do you know the code doesn't work ?

Your actions and replies are all indicative of a FORUM TROLL. The volunteers on this and other forums willing donate their
time and efforts free of charge. Most volunteers are gainfully employed so their efforts here are completed when they have
some free time from their employers needs.

What you are doing is 'playing' with the volunteers which is not appreciated.

Best wishes on securing an answer.

Volunteers beware !
 
Logit said:
If you dont have a workbook yet ... how do you know the code doesn't work ?

Your actions and replies are all indicative of a FORUM TROLL. The volunteers on this and other forums willing donate their
time and efforts free of charge. Most volunteers are gainfully employed so their efforts here are completed when they have
some free time from their employers needs.

What you are doing is 'playing' with the volunteers which is not appreciated.

Best wishes on securing an answer.

Volunteers beware !
Click to expand...
No, don't get me wrong. I attached a workbook where you helped me with the code. However, I will use these codes through many other workbooks.

At least I attached a workbook for you earlier, but I have more to work on. So please don't think I'm just wasting your time. And even before we all start to work on something, we first picture it in our minds before going behind the PC to work.
In summary, I will create more than 10 workbooks where I will use these formulas you're helping me with. So please don't get me wrong.

At least I have already attached one of the workbooks above. This platform has helped me a lot and made me the best excel guy wherever I go to. So please don't think I'm using this platform in a wrong way. Don't get me wrong please .. Thank you.
 
You must log in or register to reply here.
Back
Top