Macro Magic Wand
 

Hi guys...

Each monday I get a spreadsheet that's 10,00 lines deep
and 100 across. My task is to locate certain values
within any cell within that sheet and then delete the row
in which the value was found. Typically I have about 300
values to find and it takles me around 4 hours - I've
reached breaking point!

Ideally I'd like to click on a macro button that will
launch an input box that will allow me to for example
enter the name Jones. When I click OK on the input box I
want a macro to run through the entire sheet locating
every occourance Jones and deleting every row in which
the value Jones appear (lifting cells upwards of course)

Are there any examples of this type of functional
programming around...can anyone help me do this? Is it
possible, can Excel VBA handle this?

Thanks in advance

Gordon.

Macro Magic Wand
 

Ideally I'd like to click on a macro button that will
launch an input box that will allow me to for example
enter the name Jones. When I click OK on the input box I
want a macro to run through the entire sheet locating
every occourance Jones and deleting every row in which
the value Jones appear (lifting cells upwards of course)

Sub DeleteRows()
Dim rng As Range
Dim c As Range
Dim str1 As String

str1 = InputBox("Delete rows with this value.")
Set rng = Sheets("Sheet1").UsedRange
For Each c In rng
If c = str1 Then Sheets("Sheet1").Rows(c.Row).Delete
Next c
End Sub

HTH,
Merjet

Macro Magic Wand
 

You can also try the following which will avoid lines being skipped after
deletion

Robert

Sub removeRows()
Dim ws As Worksheet
Dim cel As Range
Dim rowss As New Collection
n = ActiveSheet.Index
Application.ScreenUpdating = False

On Error Resume Next
For Each ws In ThisWorkbook.Sheets
matrix = ""

ws.Activate

For Each cel In ws.UsedRange
If Not IsEmpty(cel.Value) And cel.Value = 0 Then
cel.Select
rowss.Add cel.Row & ":" & cel.Row, CStr(cel.Row & ":" & cel.Row)
End If
Next

If rowss.Count 0 Then
For j = 1 To rowss.Count
matrix = matrix & rowss(j) & ","
Debug.Print matriz
Next
End If

For k = 1 To rowss.Count
n = rowss.Count
rowss.Remove (n)
Next

If Not IsEmpty(matrix) Then
matrix = Left(matrix, Len(matrix) - 1)
Range(matrix).Select
Selection.Delete Shift:=xlUp
End If
Range("A1").Select
Next

On Error Resume Next

Sheets(n).Activate

Application.ScreenUpdating = True
End Sub

Macro Magic Wand
 

Try this.... it give you the count of removed rows at the end.

Public Sub remove()
Worksheets("Sheet1").Activate
Dim lastrow As Long
Dim lastcol As Long
Dim sString As String
sString = InputBox("Delete Row(s) were cell has this value.")
lastrow = ActiveSheet.UsedRange.Rows.Count
lastcol = ActiveSheet.UsedRange.Columns.Count
Application.ScreenUpdating = False
Dim ir As Long, ic As Long, rd As Long
For ir = lastrow To 1 Step -1
For ic = lastcol To 1 Step -1
If Cells(ir, ic).Value = sString Then
Rows(ir).Delete shift:=xlUp
rd = rd + 1
End If
Next ic
Next ir
Application.ScreenUpdating = True
MsgBox "Deleted: " & rd & " rows"
End Sub

Cheers
Nigel

"Gordon" wrote in message
...
Hi guys...

Each monday I get a spreadsheet that's 10,00 lines deep
and 100 across. My task is to locate certain values
within any cell within that sheet and then delete the row
in which the value was found. Typically I have about 300
values to find and it takles me around 4 hours - I've
reached breaking point!

Ideally I'd like to click on a macro button that will
launch an input box that will allow me to for example
enter the name Jones. When I click OK on the input box I
want a macro to run through the entire sheet locating
every occourance Jones and deleting every row in which
the value Jones appear (lifting cells upwards of course)

Are there any examples of this type of functional
programming around...can anyone help me do this? Is it
possible, can Excel VBA handle this?

Thanks in advance

Gordon.

Macro Magic Wand
 

Nigel...

I'm a sniff away from from making you code work for me.
The problem that I have is that the code cycles through
fine but doesn't locate the value that I asked it to
despite being case sensitive. Could the problem be that
my values are text strings?

For example I need to locate the value London...I enter
this into the input box but returns that no rows have
been deleted (so therefore no London (s) found.

What am I doing wrong..?

Thanks for your help buddy...

GOrdon


-----Original Message-----
Try this.... it give you the count of removed rows at
the end.

Public Sub remove()
Worksheets("Sheet1").Activate
Dim lastrow As Long
Dim lastcol As Long
Dim sString As String
sString = InputBox("Delete Row(s) were cell has this
value.")
lastrow = ActiveSheet.UsedRange.Rows.Count
lastcol = ActiveSheet.UsedRange.Columns.Count
Application.ScreenUpdating = False
Dim ir As Long, ic As Long, rd As Long
For ir = lastrow To 1 Step -1
For ic = lastcol To 1 Step -1
If Cells(ir, ic).Value = sString Then
Rows(ir).Delete shift:=xlUp
rd = rd + 1
End If
Next ic
Next ir
Application.ScreenUpdating = True
MsgBox "Deleted: " & rd & " rows"
End Sub

Cheers
Nigel

"Gordon" wrote in
message
...
Hi guys...

Each monday I get a spreadsheet that's 10,00 lines deep
and 100 across. My task is to locate certain values
within any cell within that sheet and then delete the
row
in which the value was found. Typically I have about
300
values to find and it takles me around 4 hours - I've
reached breaking point!

Ideally I'd like to click on a macro button that will
launch an input box that will allow me to for example
enter the name Jones. When I click OK on the input box
I
want a macro to run through the entire sheet locating
every occourance Jones and deleting every row in which
the value Jones appear (lifting cells upwards of
course)

Are there any examples of this type of functional
programming around...can anyone help me do this? Is it
possible, can Excel VBA handle this?

Thanks in advance

Gordon.


.

Macro Magic Wand
 

I expect the probelm lies with trailing spaces, since these can aslobe put
in the input string I have modified both with a trim function. It should
now work in these case as well. It does not take account of case but you
could also put in a ucase function as well and that would remove this
sensitivity as well. Cheers

Public Sub remove()
Worksheets("Sheet1").Activate
Dim lastrow As Long
Dim lastcol As Long
Dim sString As String
sString = Trim(InputBox("Delete Row(s) were cell has this value."))
lastrow = ActiveSheet.UsedRange.Rows.Count
lastcol = ActiveSheet.UsedRange.Columns.Count
Application.ScreenUpdating = False
Dim ir As Long, ic As Long, rd As Long
For ir = lastrow To 1 Step -1
For ic = lastcol To 1 Step -1
If Trim(Cells(ir, ic).Value) = sString Then
Rows(ir).Delete shift:=xlUp
rd = rd + 1
End If
Next ic
Next ir
Application.ScreenUpdating = True
MsgBox "Deleted: " & rd & " rows"
End Sub


wrote in message
...
Nigel...

I'm a sniff away from from making you code work for me.
The problem that I have is that the code cycles through
fine but doesn't locate the value that I asked it to
despite being case sensitive. Could the problem be that
my values are text strings?

For example I need to locate the value London...I enter
this into the input box but returns that no rows have
been deleted (so therefore no London (s) found.

What am I doing wrong..?

Thanks for your help buddy...

GOrdon


-----Original Message-----
Try this.... it give you the count of removed rows at
the end.

Public Sub remove()
Worksheets("Sheet1").Activate
Dim lastrow As Long
Dim lastcol As Long
Dim sString As String
sString = InputBox("Delete Row(s) were cell has this
value.")
lastrow = ActiveSheet.UsedRange.Rows.Count
lastcol = ActiveSheet.UsedRange.Columns.Count
Application.ScreenUpdating = False
Dim ir As Long, ic As Long, rd As Long
For ir = lastrow To 1 Step -1
For ic = lastcol To 1 Step -1
If Cells(ir, ic).Value = sString Then
Rows(ir).Delete shift:=xlUp
rd = rd + 1
End If
Next ic
Next ir
Application.ScreenUpdating = True
MsgBox "Deleted: " & rd & " rows"
End Sub

Cheers
Nigel

"Gordon" wrote in
message
...
Hi guys...

Each monday I get a spreadsheet that's 10,00 lines deep
and 100 across. My task is to locate certain values
within any cell within that sheet and then delete the
row
in which the value was found. Typically I have about
300
values to find and it takles me around 4 hours - I've
reached breaking point!

Ideally I'd like to click on a macro button that will
launch an input box that will allow me to for example
enter the name Jones. When I click OK on the input box
I
want a macro to run through the entire sheet locating
every occourance Jones and deleting every row in which
the value Jones appear (lifting cells upwards of
course)

Are there any examples of this type of functional
programming around...can anyone help me do this? Is it
possible, can Excel VBA handle this?

Thanks in advance

Gordon.


.