VBA macro delete row - Help needed

[gemiho]

Hello everyone,

I wonder if you please could help me with a macro; I just started to us
VBA, so my knowledge is equal zero.
I have a workbook with a few sheets. I am trying to write a macro tha
after clicking on a cell it deletes the entire row if the cell in colum
A does not contain any text; actually I wanted it to do not delete th
row if the cell in column A contains the text “keepThisRow”, but I d
not know how to do it. I was thinking to use a Form button.

BellowI the code I have, but it does not work at all. thank you i
advance for all help

Sub deleteRow_Click()
Dim rng As Range
ActiveSheet.Unprotect Password:="123"
On Error GoTo ErrHandler

Set rng
Worksheets(ActiveSheet).Range("A2:A500").ActiveCell.Row.Select 'I wan
to select a cell in row I want to delete
If Not rng Is Nothing Then
rng.EntireRow.Delete xlUp
End If
Exit Sub
ErrHandler:

ActiveSheet.Protect Password:="123", DrawingObjects:=True
Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True
AllowSorting:=True
End Su

 

[Auric__]

I wonder if you please could help me with a macro; I just started to use
VBA, so my knowledge is equal zero.
I have a workbook with a few sheets. I am trying to write a macro that
after clicking on a cell it deletes the entire row if the cell in column
A does not contain any text; actually I wanted it to do not delete the
row if the cell in column A contains the text “keepThisRow”, but I do
not know how to do it. I was thinking to use a Form button.

BellowI the code I have, but it does not work at all. thank you in
advance for all help

If you're thinking about using a form specifically because you don't know how
to do it automatically, you can put it in Worksheet_SelectionChange, in the
sheet's object, declared like so:

Private Sub Worksheet_SelectionChange (ByVal Target As Range)

Sub deleteRow_Click()
Dim rng As Range
ActiveSheet.Unprotect Password:="123"

Note that putting the password here lets anyone who can view your code see
it.

On Error GoTo ErrHandler

Set rng =
Worksheets(ActiveSheet).Range("A2:A500").ActiveCell.Row.Select 'I want
to select a cell in row I want to delete
If Not rng Is Nothing Then
rng.EntireRow.Delete xlUp
End If

Change the above block (from "Set rng =" to "End If") to this:

If Len(Cells(ActiveCell.Row, 1).Value) < 1 Then _
ActiveCell.EntireRow.Delete xlUp

(Note that this will delete the row if cell A contains a formula that
evaluates to an empty string: "".)

If you want to delete the row if column A is *anything* but "keepThisRow",
use this instead:

Exit Sub

By exiting the sub in this manner, you aren't re-protecting the page. If you
*want* it protected afterward, delete the above line.

 

[Howard]

Hello everyone,



I wonder if you please could help me with a macro; I just started to use

VBA, so my knowledge is equal zero.

I have a workbook with a few sheets. I am trying to write a macro that

after clicking on a cell it deletes the entire row if the cell in column

A does not contain any text; actually I wanted it to do not delete the

row if the cell in column A contains the text “keepThisRow”, but I do

not know how to do it. I was thinking to use a Form button.



BellowI the code I have, but it does not work at all. thank you in

advance for all help



Sub deleteRow_Click()

Dim rng As Range

ActiveSheet.Unprotect Password:="123"

On Error GoTo ErrHandler



Set rng =

Worksheets(ActiveSheet).Range("A2:A500").ActiveCell.Row.Select 'I want

to select a cell in row I want to delete

If Not rng Is Nothing Then

rng.EntireRow.Delete xlUp

End If

Exit Sub

ErrHandler:



ActiveSheet.Protect Password:="123", DrawingObjects:=True,

Contents:=True, Scenarios:=True _

, AllowFormattingCells:=True,

AllowSorting:=True

End Sub

Hi gemiho


Option Explicit
Option Compare Text

Sub KeepRow()
Dim c As Range

'Password stuff here

For Each c In Range("A2:A500")
If c.Value <> "keep this row" Then c.EntireRow.Delete
Next

'Password stuff here
End Sub

Regards,
Howard

 

[Claus Busch]

Hi,

Am Mon, 20 May 2013 04:38:43 +0100 schrieb gemiho:

I wonder if you please could help me with a macro; I just started to use
VBA, so my knowledge is equal zero.
I have a workbook with a few sheets. I am trying to write a macro that
after clicking on a cell it deletes the entire row if the cell in column
A does not contain any text; actually I wanted it to do not delete the
row if the cell in column A contains the text “keepThisRow”, but I do
not know how to do it. I was thinking to use a Form button.

If you have no headers in your table change A2 to A1:

Sub DeleteRows()
Dim LRow As Long

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Unprotect "123"
.UsedRange.AutoFilter Field:=1, Criteria1:= _
"<>*ThisRow*"
.Range("A2:A2" & LRow).EntireRow.Delete
.AutoFilterMode = False
.Protect "123"
End With
End Sub


Regards
Claus Busch

 

[Claus Busch]

Hi,

Am Mon, 20 May 2013 11:00:17 +0200 schrieb Claus Busch:

.UsedRange.AutoFilter Field:=1, Criteria1:= _
"<>*ThisRow*"

change the line above to:
..UsedRange.AutoFilter Field:=1, Criteria1:= _
"<>*keepThisRow*"


Regards
Claus Busch

 

[gemiho]

Hi gemiho


Option Explicit
Option Compare Text

Sub KeepRow()
Dim c As Range

'Password stuff here

For Each c In Range("A2:A500")
If c.Value <> "keep this row" Then c.EntireRow.Delete
Next

'Password stuff here
End Sub

Regards,
Howard

Good morning,

Thank you so much Auric and Howard for the very fast answers
Unfortunately I have to go to work now and I cannot test your code
until later when I get back home. I am posting this to clarify som
things:

I use a form because I do not know VBA and after researching many hour
this is the only I could do it; shame on me. If you know a better wa
please do not hesitate to tell me.

I am aware that putting the password in the code like that lets anyon
who can view the code see it, but I do not know how to do it in othe
way. For that reason I was thinking to lock the VBA project for viewin
using a different password. Once again, if you know a better way pleas
let me know.

Column A does not contain anything, it is totally empty The formulas ar
in column S and it is locked; all the cells except the table are
(B4:R?), are locked. Columns B to R contain validation lists and user
can enter data in them. Since all the sheets are protected and users ca
only "Select unlocked cells", "Format cells" and "Sort" there is a "Ad
row" button that can add empty rows to the table and it copies th
formulas to the new rows. Below the table there are rows containin
formulas and they should not be deleted. I was thinking to leave colum
A empty in the table area, but put "keepThisRow" in all other cells i
column A below the table, in that way the "Delete Row" button should no
be able to delete them.

I do need the sheet password protected after the macro has delete
rows.

Thanks again for you very fast answers and have a great day

 

[GS]

<FWIW>
Here's a trimmed down version of what I use for setting generic sheet
protection. It allows making changes via code without having to toggle
protection off/on. Unfortunately, the parameter that makes this
possible (UserInterfaceOnly) does not persist between runtimes and so
protection must be reset every time the workbook is opened, by running
the 'ResetProtection' routine at startup from the Workbook_Open event
or the Autpen sub...


Public Const PWD$ = "123" '//edit to suit
Sub wksProtect(Optional Wks As Worksheet)
' Protects specified sheets according to Excel version.
' Assumes Public Const PWRD as String contains the password, even if
there isn't one.
'
' Arguments: Wks [In] Optional. Ref to the sheet to be protected.
' (Defaults to ActiveSheet if missing)

If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
With Wks
If Val(Application.VERSION) >= 10 Then
'Copy/paste the desired parameters above the commented line.
.Protect Password:=PWRD, _
DrawingObjects:=True, Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True, _
AllowFiltering:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowFormattingCells:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True ', _
AllowInsertingColumns:=True, _
AllowInsertingHyperlinks:=True, _
AllowInsertingRows:=True, _
AllowUsingPivotTables:=True
Else
.Protect Password:=PWRD, _
DrawingObjects:=False, Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True
End If
' .EnableAutoFilter = True
.EnableOutlining = True

' .EnableSelection = xlNoRestrictions
.EnableSelection = xlUnlockedCells
' .EnableSelection = xlNoSelection
End With

End Sub

Sub ResetProtection(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
Wks.Unprotect PWRD: wksProtect Wks
End Sub

To use for a single sheet named "Sheet1" (as opposed to all sheets)...

ResetProtection Sheets("Sheet1")

To use at startup...

Call ProtectAllSheets

Sub ProtectAllSheets()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
ResetProtection wks
Next 'wks
End Sub


Note that I have configured the 'wksProtect' procedure to apply your
posted settings by including all the desired options above the comment
flag (apostrophe after 'AllowDeletingRows').

How this works is by shifting the parameters around so those that you
want to apply are above the commented out parameters. I no longer use
this approach in non-trivial projects since I have developed a more
efficient methodology that stores protection settings in a local scope
defined name for sheets that require protection. This allows me to
customize the protection parameters for each sheet specific to
context/need as opposed to a generic setting for all sheets. If anyone
is interested in going with such an approach I can post details on
request...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[gemiho]

Hi
Thank you all for your help; I really appreciate it. I just started t
try the different macros to see which one works better for me. But
need to get them working first (remember that I do not know VBA).

I have so problems and I wonder if you could help me again.

Auric’s macro:
For some reason sometimes it does not work as it should and it delete
the rows that have “keepThisRow” in column A; the rows are the ones a
the end of the table. Can you please tell me what I did wrong? I attac
a drawing so you can see the rows that should not be deleted.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
Sub deleteRow_Click()
'
Dim rng As Range
ActiveSheet.Unprotect Password:="123"
'
On Error GoTo ErrHandler
'
If Cells(ActiveCell.Row, 1).Value <> "keepThisRow" Then _
ActiveCell.EntireRow.Delete xlUp
'
ErrHandler:
'
ActiveSheet.Protect Password:="123", DrawingObjects:=True
Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowSorting:=True
'
End Su