Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - adding a picture that will pop up when I wand over it? | Excel Worksheet Functions | |||
Magic Formula Appearance | New Users to Excel | |||
IDE add-on VB Magic? | Excel Discussion (Misc queries) | |||
Magic Cells | Excel Discussion (Misc queries) | |||
Magic Shrinking Listbox | Excel Programming |