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").ActiveCel l.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

gemiho wrote:

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").ActiveCel l.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:

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

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.

ErrHandler:

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

--
WARNING: Continuous drinking may lead to continuous drinking.

On Sunday, May 19, 2013 8:38:43 PM UTC-7, gemiho wrote:
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").ActiveCel l.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









--

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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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 codes until later when I get back home. I am posting this to clarify some things:

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

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

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

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

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

<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 Auto_Open 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

Hi
Thank you all for your help; I really appreciate it. I just started to try the different macros to see which one works better for me. But I 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 deletes the rows that have “keepThisRow” in column A; the rows are the ones at the end of the table. Can you please tell me what I did wrong? I attach 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 Sub


Could someone please tell me what I have to do in for example Auric’s macro so de delete button does not delete the last row in the table? If I write in column A "keepThisRow" it does not delete it, but when I add new rows using the "Add Row" button it copies “keepThisRow” to column A in the new rows, and then I cannot delete them.