how to delete Selected row , but if selected row is First row or last row then dont delete in Excel VBA (Macro)

Question

0.00/5 (No votes)

See more:

VB

Sub deleterow()

ActiveSheet.Unprotect

Dim MyRange As Object
    ' Store the selected range in a variable.
    Set MyRange = Selection
    
    
    
    ' Select the entire column.
    Selection.EntireRow.Select
    
      
    ' Insert Columns in all selected sheets.
    Selection.delete
    
    ' Reselect the previously selected cells.
    
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    

End Sub

Posted 20-Aug-15 2:44am

HimenDev

Updated 20-Aug-15 2:58am

_Asif_

v2

Add a Solution

Comments

Maciej Los 20-Aug-15 9:22am

Are you sure that first and last row can't be deleted?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-08-20T03:31:00

Solution 1

I'll improve your code to 3 lines:

VB

ActiveSheet.Unprotect
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
ActiveSheet.Protect ...

For further information, please see: Range.Delete Method (Excel)[^]

Note: your code is potentially dangerous, because it works no matter of context! What it means? It deletes row from currently active sheet. If you would like to delete row from desired sheet, use something like this:

VB

Dim wsh As Worksheet
'...
Set wsh = ThisWorkbook.Worksheets("Sheet1")
wsh.ActiveCell.EntireRow.Delete Shift:=xlShiftUp
'...

Posted 20-Aug-15 3:31am

Maciej Los

Updated 20-Aug-15 3:32am

v2

Comments

Wendelius 20-Aug-15 14:04pm

Nice answer, 5

Maciej Los 20-Aug-15 17:02pm

Thank you, Mika.

HimenDev 21-Aug-15 0:40am

actually i want to put condition for first row and last row...
that if my selected row is first row or last row then dont delete otherwise it should be deleted...
i dont have any issue regarding sheet ..i have only one sheet available so...

HimenDev 21-Aug-15 0:41am

i want one more thing...like if my selected row contains particular word then dont delete otherwise delete selected row....

Maciej Los 23-Aug-15 15:36pm

I don't get you ;(