• 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.

delete sheet1 row based on sheet2 data

L

Leonardo1234

Member
see the data in sheet2
what I wanted is see the row number after( = ) and delete that row in sheet1
I have to do all this by vba only
I have highlighted the data which we will delete , I have highlighted the same in sample file but in actual file there will be no highlighted colour it is only for understanding purpose
 

Attachments

  • File2.xlsm
    14 KB · Views: 9
According to the original attachment as a beginner starter :​
Code: 
Sub Demo1()
        Dim R%(), V, C&
        Application.ScreenUpdating = False
With Sheet1.UsedRange.Rows
        ReDim R(1 To .Count, 0)
    For Each V In Sheet2.[A1].CurrentRegion.Value
        R(Split(V, "Row")(2), 0) = 1
    Next
        C = .Columns.Count + 1
    With .Resize(, C)
         .Columns(C).Value = R
         .Sort .Cells(C), xlAscending, Header:=xlNo
       Union(.Columns(C), .Item(Application.Match(1, .Columns(C), 0) & ":" & .Count)).Clear
    End With
End With
        Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !
​
 
Leonardo1234
That would be a challenge!
If it need to 'run-only-once' then
it would need to delete sheet2 and
all Your backups,
that You cannot run it again!
That won't be nice!
... but even this would help something.
You would learn to be patient,
before push anything,
before You're 100% sure,
what will happen!
' measure twice ... cut once '
 

Attachments

  • File2.xlsm
    48.3 KB · Views: 1
No problem but i think we have a solution put something in the code like after deleting the data in sheet1 delete sheet2
i am fine with the previous solution but if u have free time then we can make a bulletproof solution for this problem and if u don't have free time then no problem i am ok with the previous solution
 
Leonardo1234
There is almost 'bulletproof' as I wrote in my previous reply.
There should add, that if You will somewhere copy that file again
... it would delete it without asking! Not nice!
>> ReRead gray text again <<
 
vletm i know that is the bulletproof solution i am ok with it
i like the way that u have guided me (providing detailed information about this code that don't run this code twice) that's y i was intersted to play with this problem
Thnx for giving ur precious time and great support to my post
Problem is already solved
 
Code: 
Sub Demo1c()
    Dim V, W, R%(), C&
    With Sheet2.[A1].CurrentRegion
        V = .Value:  If IsEmpty(V) Then Beep: Exit Sub
       .Cut .Cells(1, 3)
    End With
        Application.ScreenUpdating = False
With Sheet1.UsedRange.Rows
        ReDim R(1 To .Count, 0)
        For Each W In V:  R(Split(W, "Row")(2), 0) = 1:  Next
        C = .Columns.Count + 1
    With .Resize(, C)
         .Columns(C).Value = R
         .Sort .Cells(C), xlAscending, Header:=xlNo
       Union(.Columns(C), .Item(Application.Match(1, .Columns(C), 0) & ":" & .Count)).Clear
    End With
End With
        Application.ScreenUpdating = True
End Sub
You may Like it !
​
 
Leonardo1234
'my code' can run as many times as want ...
Normally, it would make that only once!
but if eg You copy those original values back to A-column and run again
- then it will do it again.
Even if that code would delete after run it ...
You would have somewhere backup and soon You would run it again!
Trust me! You don't want to get 'bulletproof'!
>> Still that gray text would be much more user friendly. <<
... there are no problems!
... sometimes, there can be challenges!
 
Both are perfect I understood what u mean to say vletm
Thnx to both of u for giving ur precious time and great support to my post
I am happy with all the solutions from start till end
 
You must log in or register to reply here.
Back
Top