VBA- Delete entire row in sheet 1
Solution 1
You only need to specify which which sheet the row is in, in addition to specifying the row as you already do. I.e.
Worksheets("Sheet1").Rows(2).EntireRow.Delete
Should do what you wish. If you have called the worksheet something different than "Sheet1", change what is inside the quotation marks. Strictly speaking you don't need to specify that it is the entire row you want to delete, as you've already said it is the row you are dealing with with Rows(2)
, but it shouldn't do any harm in there either.
If you additionally want to do the deletion from a different workbook (or just make sure that the deletion takes place in the correct workbook), you can also specify the workbook-name:
Workbooks("Workbookname.xlsm").Worksheets("Sheet1").Rows(2).EntireRow.Delete
Solution 2
If your button is in any other sheet and you wants to delete a row from another sheet, you simply have to activate that sheet. For example, you are trying to delete row from Sheet1 then you can activate the sheet by this one-liner.
Sheets(0).Activate
Related videos on Youtube
kgkmeekg
Lowly engineer seeking mathematics and computer knowledge. Currently a student, I am actively working on the intersection of: Artificial Intelligence and Cyber Security I am a strong believer of the open source ideology and try to share as much knowledge as possible.
Updated on May 25, 2022Comments
-
kgkmeekg over 1 year
I have been using the following to delete a row.
Rows([2]).EntireRow.Delete
But this was when my workbook had only one sheet. A command button has been added in sheet2 which calls the macro present in sheet1.
How should I modify my above code so that the VBA deletes the second row of sheet1 ?
-
Vasily over 8 yearswhy you use
EntireRow
if you already specify the row number,EntireRow
used in case when required to determinate the row of the range,Range("A1").EntireRow
for instance -
eirikdaude over 8 yearsIt is what is used by the OP, keeping the code as similar as possible to what is already used will make it slightly easier to understand from OP's perspective, especially as there's not really any good reason to change it. @Vasily
-
eirikdaude over 8 yearsActivating the sheet instead of referencing the range you want to edit specifically isn't considered good practice. Neither is there any guarantee that the sheet the OP wants to work on is the one with an index of 0.
-
Akif Patel - BRMS over 8 yearsAgreed. However, it was just an example to show how to activate a sheet and work on it.
-
Vasily over 8 yearsIn this case, the code is considered as overweighted, so it mean in simple words oil in oil, but anyway this your answer and you can use or not use remarks from another users
-
kgkmeekg over 8 yearsEirik your code is working. It had slipped out of my mind that i had changed the name of my sheet.
-
kgkmeekg over 8 yearsSo to activate my sheet1 named as "DAT_FL" do I need write Sheets(1).Activate
-
kgkmeekg over 8 yearsIs there a way of knowing the index number of my sheet?
-
Akif Patel - BRMS over 8 yearsYes. by Name
Sheets("Sheet2").Activate
and get Index and activate by Indexind = Sheets("Sheet3").Index Sheets(ind).Activate
-
kgkmeekg over 8 yearsThanks Akif. Although activate by name is not working but activate through index works like a dream.