VBA- Delete entire row in sheet 1

12,248

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
Share:
12,248

Related videos on Youtube

kgkmeekg
Author by

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, 2022

Comments

  • kgkmeekg
    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
    Vasily over 8 years
    why 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
    eirikdaude over 8 years
    It 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
    eirikdaude over 8 years
    Activating 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
    Akif Patel - BRMS over 8 years
    Agreed. However, it was just an example to show how to activate a sheet and work on it.
  • Vasily
    Vasily over 8 years
    In 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
    kgkmeekg over 8 years
    Eirik your code is working. It had slipped out of my mind that i had changed the name of my sheet.
  • kgkmeekg
    kgkmeekg over 8 years
    So to activate my sheet1 named as "DAT_FL" do I need write Sheets(1).Activate
  • kgkmeekg
    kgkmeekg over 8 years
    Is there a way of knowing the index number of my sheet?
  • Akif Patel - BRMS
    Akif Patel - BRMS over 8 years
    Yes. by Name Sheets("Sheet2").Activate and get Index and activate by Index ind = Sheets("Sheet3").Index Sheets(ind).Activate
  • kgkmeekg
    kgkmeekg over 8 years
    Thanks Akif. Although activate by name is not working but activate through index works like a dream.