Macro Magic Wand

G

Gordon

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.
 
C

CoRrRan

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.
Click to expand...

Gordon,

MS MVP Jan-Karel Pieterse has a utility called "FlexFind", this
utility will find all instances of "Jones" in your spreadsheet. There
is a downside to this application, you can't look into the code.
Therefore, you can't manually enter code that will delete the rows
that contain "Jones". But it might help you with your problem.

Perhaps you could ask Mr Pieterse for help, his email is on the site
as well. He might give you a clue as what to do after the program has
run...

http://www.jkp-ads.com/OfficeMarketPlaceFF-EN.htm

HTH,

CoRrRan
 
M

merjet

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)
Click to expand...

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
 
C

cmart02

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
 
N

Nigel

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
 
G

Guest

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
 
N

Nigel

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
 
Top