VBA macro delete row - Help needed

[gemiho]

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




--
gemiho

[Auric__]

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

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.

[Howard]

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

[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*"

[gemiho]

Howard;1611930 Wrote:
> On Sunday, May 19, 2013 8:38:43 PM UTC-7, gemiho wrote:-

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




--
gemiho

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

[gemiho]

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

'