Sub STEP17()
Dim wb As Workbook, wk As Workbook
Set wb = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\HotStocks\Alert.xlsx")
Set wk = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\HotStocks\AlertCodes.xlsx")
wb.Activate
lrow = wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
lrow1 = wk.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lrow1
For j = 2 To lrow
If wb.Sheets(1).Range("B" & j).Value = wk.Sheets(2).Range("B" & i).Value Then
wb.Sheets(1).Range("i" & j).EntireRow.Delete
End If
Next
Next
wb.Close SaveChanges:=True
wk.Close SaveChanges:=True
End Sub
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Looking to make this macro Faster
0
Discussion
When deleting rows you must always work from bottom up, not first row to last. This is because when you delete a row all rows below it will change number.
Variatus (rep: 4889) Sep 25, '20 at 7:59 pm
Answers
0
Selected Answer
Next time, please include more than just a title and code. This is how you make it faster:
Change your loop to work like this
Dim myArray() As Variant
Dim iRow As Long
Dim iCol As Long
myArray = Range("A1:B2").Value
' Loop through the Rows
For iRow = LBound(myArray, 1) To UBound(myArray, 1)
' Loop through the Columns
For iCol = LBound(myArray, 2) To UBound(myArray, 2)
' Print value of cell into immediate window.
Debug.Print myArray(iRow, iCol)
' Do what you want.
Next iCol
Next iRowWhere it says "Do what you want" I would store the rows to delete in another array and then delete them after the loop because the delete operations, if you have many, will still take a lot of time.
Discussion
Thnx Sir but can u add these lines in the macro & plz edit my macro bcoz i am confused about editing the macro
Plz....................
Plz....................
rider1234 (rep: 10) Sep 25, '20 at 4:22 am
Thnx Alot Don Sir & Variatus Mam for guiding me in making this macro Faster
Have a Awesome Day
Have a Awesome Day
rider1234 (rep: 10) Sep 28, '20 at 12:52 am
Youre welcome! I'm sorry I couldnt help more but I'm on the road and haven't had time to integrate the code into yours.
don (rep: 1989) Sep 30, '20 at 9:59 am