Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with VBA code
What do I need to do to this VBA code so when I start it it applies to the
entire workbook rather than the worksheet? Thank you. Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error Goto 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with VBA code
Hi
You would need to wrap your code in a For Next loop. Add these lines after your existing Dim statements Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Activate Range("E1").Select your existing code then before Application.ScreenUpdating=True put Next -- Regards Roger Govier "LiveUser" wrote in message ... What do I need to do to this VBA code so when I start it it applies to the entire workbook rather than the worksheet? Thank you. Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error Goto 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with VBA code
Hi,
Try iterating through each sheet in the workbook: Dim wksht As Worksheet For Each wksht In ActiveWorkbook.Worksheets <<your code Next wksht HTH Simon LiveUser wrote: What do I need to do to this VBA code so when I start it it applies to the entire workbook rather than the worksheet? Thank you. Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error Goto 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Application.ScreenUpdating = True End Sub -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200801/1 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with VBA code
smw226 via OfficeKB.com and Roger Govier,
I didn't have any luck with the code you gave me. I tried moving it around a bit also, but couldn't get it to work. I don't know what I could be doing wrong. "LiveUser" wrote: What do I need to do to this VBA code so when I start it it applies to the entire workbook rather than the worksheet? Thank you. Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error Goto 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Application.ScreenUpdating = True End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with VBA code
Hi
I shouldn't have include the line Range("E1").Select -- Regards Roger Govier "LiveUser" wrote in message ... smw226 via OfficeKB.com and Roger Govier, I didn't have any luck with the code you gave me. I tried moving it around a bit also, but couldn't get it to work. I don't know what I could be doing wrong. "LiveUser" wrote: What do I need to do to this VBA code so when I start it it applies to the entire workbook rather than the worksheet? Thank you. Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error Goto 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Application.ScreenUpdating = True End Sub |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with VBA code
I still couldn't get it to work. Here is what I have:
Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Activate 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error GoTo 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Next Application.ScreenUpdating = True End Sub "Roger Govier" wrote: Hi I shouldn't have include the line Range("E1").Select -- Regards Roger Govier "LiveUser" wrote in message ... smw226 via OfficeKB.com and Roger Govier, I didn't have any luck with the code you gave me. I tried moving it around a bit also, but couldn't get it to work. I don't know what I could be doing wrong. "LiveUser" wrote: What do I need to do to this VBA code so when I start it it applies to the entire workbook rather than the worksheet? Thank you. Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error Goto 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Application.ScreenUpdating = True End Sub |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with VBA code
Hi
In what way does it not work? What error message do you get? If it worked for your individual sheet, what is different about your other sheets? The only difference to the original code you posted, is making the macro loop through each worksheet in the workbook in turn, then running your macro. -- Regards Roger Govier "LiveUser" wrote in message ... I still couldn't get it to work. Here is what I have: Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Activate 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error GoTo 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Next Application.ScreenUpdating = True End Sub "Roger Govier" wrote: Hi I shouldn't have include the line Range("E1").Select -- Regards Roger Govier "LiveUser" wrote in message ... smw226 via OfficeKB.com and Roger Govier, I didn't have any luck with the code you gave me. I tried moving it around a bit also, but couldn't get it to work. I don't know what I could be doing wrong. "LiveUser" wrote: What do I need to do to this VBA code so when I start it it applies to the entire workbook rather than the worksheet? Thank you. Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error Goto 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Application.ScreenUpdating = True End Sub |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with VBA code
With the code I just posted:
I select a cell (A1). Inside the cell is the word yellow. I start the macro and I get a popup window - "Enter Search Column" - which shows (A) being the selected column. I click OK. I get a popup window - "Enter Search String" - Nothing shows up, like it does without your additional code, so I manually enter in the information from (A1) - Yellow. I click OK I get a popup window - "Do you really want to delete rows with empty cells?" - what is automatically entered is "No". I click OK. Nothing happens. Cell (A1) is still there and no rows throughout the workbook have been deleted. The macro has stopped. Thank you. "Roger Govier" wrote: Hi In what way does it not work? What error message do you get? If it worked for your individual sheet, what is different about your other sheets? The only difference to the original code you posted, is making the macro loop through each worksheet in the workbook in turn, then running your macro. -- Regards Roger Govier "LiveUser" wrote in message ... I still couldn't get it to work. Here is what I have: Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Activate 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error GoTo 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Next Application.ScreenUpdating = True End Sub "Roger Govier" wrote: Hi I shouldn't have include the line Range("E1").Select -- Regards Roger Govier "LiveUser" wrote in message ... smw226 via OfficeKB.com and Roger Govier, I didn't have any luck with the code you gave me. I tried moving it around a bit also, but couldn't get it to work. I don't know what I could be doing wrong. "LiveUser" wrote: What do I need to do to this VBA code so when I start it it applies to the entire workbook rather than the worksheet? Thank you. Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error Goto 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Application.ScreenUpdating = True End Sub |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with VBA code
Hi
If it is A1 on each sheet that is wanted, then after the line ws.activate Range("A1").Select -- Regards Roger Govier "LiveUser" wrote in message ... With the code I just posted: I select a cell (A1). Inside the cell is the word yellow. I start the macro and I get a popup window - "Enter Search Column" - which shows (A) being the selected column. I click OK. I get a popup window - "Enter Search String" - Nothing shows up, like it does without your additional code, so I manually enter in the information from (A1) - Yellow. I click OK I get a popup window - "Do you really want to delete rows with empty cells?" - what is automatically entered is "No". I click OK. Nothing happens. Cell (A1) is still there and no rows throughout the workbook have been deleted. The macro has stopped. Thank you. "Roger Govier" wrote: Hi In what way does it not work? What error message do you get? If it worked for your individual sheet, what is different about your other sheets? The only difference to the original code you posted, is making the macro loop through each worksheet in the workbook in turn, then running your macro. -- Regards Roger Govier "LiveUser" wrote in message ... I still couldn't get it to work. Here is what I have: Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Activate 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error GoTo 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Next Application.ScreenUpdating = True End Sub "Roger Govier" wrote: Hi I shouldn't have include the line Range("E1").Select -- Regards Roger Govier "LiveUser" wrote in message ... smw226 via OfficeKB.com and Roger Govier, I didn't have any luck with the code you gave me. I tried moving it around a bit also, but couldn't get it to work. I don't know what I could be doing wrong. "LiveUser" wrote: What do I need to do to this VBA code so when I start it it applies to the entire workbook rather than the worksheet? Thank you. Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error Goto 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Application.ScreenUpdating = True End Sub |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with VBA code
Roger,
It could be any cell that is selected. I used A1 as an example. Thank you. "Roger Govier" wrote: Hi If it is A1 on each sheet that is wanted, then after the line ws.activate Range("A1").Select -- Regards Roger Govier "LiveUser" wrote in message ... With the code I just posted: I select a cell (A1). Inside the cell is the word yellow. I start the macro and I get a popup window - "Enter Search Column" - which shows (A) being the selected column. I click OK. I get a popup window - "Enter Search String" - Nothing shows up, like it does without your additional code, so I manually enter in the information from (A1) - Yellow. I click OK I get a popup window - "Do you really want to delete rows with empty cells?" - what is automatically entered is "No". I click OK. Nothing happens. Cell (A1) is still there and no rows throughout the workbook have been deleted. The macro has stopped. Thank you. "Roger Govier" wrote: Hi In what way does it not work? What error message do you get? If it worked for your individual sheet, what is different about your other sheets? The only difference to the original code you posted, is making the macro loop through each worksheet in the workbook in turn, then running your macro. -- Regards Roger Govier "LiveUser" wrote in message ... I still couldn't get it to work. Here is what I have: Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Activate 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error GoTo 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Next Application.ScreenUpdating = True End Sub "Roger Govier" wrote: Hi I shouldn't have include the line Range("E1").Select -- Regards Roger Govier "LiveUser" wrote in message ... smw226 via OfficeKB.com and Roger Govier, I didn't have any luck with the code you gave me. I tried moving it around a bit also, but couldn't get it to work. I don't know what I could be doing wrong. "LiveUser" wrote: What do I need to do to this VBA code so when I start it it applies to the entire workbook rather than the worksheet? Thank you. Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error Goto 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Application.ScreenUpdating = True End Sub |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with VBA code
Hi
I'm sorry, but I cannot answer your problem. If your code works for a single sheet, then it SHOULD work when cycling through each other sheet within the Workbook. Perhaps someone else has an answer for this. -- Regards Roger Govier "LiveUser" wrote in message ... Roger, It could be any cell that is selected. I used A1 as an example. Thank you. "Roger Govier" wrote: Hi If it is A1 on each sheet that is wanted, then after the line ws.activate Range("A1").Select -- Regards Roger Govier "LiveUser" wrote in message ... With the code I just posted: I select a cell (A1). Inside the cell is the word yellow. I start the macro and I get a popup window - "Enter Search Column" - which shows (A) being the selected column. I click OK. I get a popup window - "Enter Search String" - Nothing shows up, like it does without your additional code, so I manually enter in the information from (A1) - Yellow. I click OK I get a popup window - "Do you really want to delete rows with empty cells?" - what is automatically entered is "No". I click OK. Nothing happens. Cell (A1) is still there and no rows throughout the workbook have been deleted. The macro has stopped. Thank you. "Roger Govier" wrote: Hi In what way does it not work? What error message do you get? If it worked for your individual sheet, what is different about your other sheets? The only difference to the original code you posted, is making the macro loop through each worksheet in the workbook in turn, then running your macro. -- Regards Roger Govier "LiveUser" wrote in message ... I still couldn't get it to work. Here is what I have: Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Activate 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error GoTo 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Next Application.ScreenUpdating = True End Sub "Roger Govier" wrote: Hi I shouldn't have include the line Range("E1").Select -- Regards Roger Govier "LiveUser" wrote in message ... smw226 via OfficeKB.com and Roger Govier, I didn't have any luck with the code you gave me. I tried moving it around a bit also, but couldn't get it to work. I don't know what I could be doing wrong. "LiveUser" wrote: What do I need to do to this VBA code so when I start it it applies to the entire workbook rather than the worksheet? Thank you. Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error Goto 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Application.ScreenUpdating = True End Sub |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with VBA code
Roger,
I do appreciate your help. I wonder if it has anything to do with Graphs in the workbook. I have graphs based on information in the workbook. It seems like it should work, I just don't know what is going on. But, Thank you for your assistance. "Roger Govier" wrote: Hi I'm sorry, but I cannot answer your problem. If your code works for a single sheet, then it SHOULD work when cycling through each other sheet within the Workbook. Perhaps someone else has an answer for this. -- Regards Roger Govier "LiveUser" wrote in message ... Roger, It could be any cell that is selected. I used A1 as an example. Thank you. "Roger Govier" wrote: Hi If it is A1 on each sheet that is wanted, then after the line ws.activate Range("A1").Select -- Regards Roger Govier "LiveUser" wrote in message ... With the code I just posted: I select a cell (A1). Inside the cell is the word yellow. I start the macro and I get a popup window - "Enter Search Column" - which shows (A) being the selected column. I click OK. I get a popup window - "Enter Search String" - Nothing shows up, like it does without your additional code, so I manually enter in the information from (A1) - Yellow. I click OK I get a popup window - "Do you really want to delete rows with empty cells?" - what is automatically entered is "No". I click OK. Nothing happens. Cell (A1) is still there and no rows throughout the workbook have been deleted. The macro has stopped. Thank you. "Roger Govier" wrote: Hi In what way does it not work? What error message do you get? If it worked for your individual sheet, what is different about your other sheets? The only difference to the original code you posted, is making the macro loop through each worksheet in the workbook in turn, then running your macro. -- Regards Roger Govier "LiveUser" wrote in message ... I still couldn't get it to work. Here is what I have: Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Activate 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error GoTo 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Next Application.ScreenUpdating = True End Sub "Roger Govier" wrote: Hi I shouldn't have include the line Range("E1").Select -- Regards Roger Govier "LiveUser" wrote in message ... smw226 via OfficeKB.com and Roger Govier, I didn't have any luck with the code you gave me. I tried moving it around a bit also, but couldn't get it to work. I don't know what I could be doing wrong. "LiveUser" wrote: What do I need to do to this VBA code so when I start it it applies to the entire workbook rather than the worksheet? Thank you. Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error Goto 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Application.ScreenUpdating = True End Sub |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with VBA code
Hi
Have you tried stepping through the Macro using F8? Press Alt+F11 to invoke the VBE Ensure that you have the Locals window showing ViewLocals Window Place your cursor anywhere within the macro code and Press F8 and step through line by line, looking at what the variables are set to in the Locals window. See if you can see where it "drops out" of the Sub an work from there. If all else fails, you can mail me a copy of the workbook and I will see if I can see what's going wrong. To mail direct, send to roger at technology4u dot co dot uk Do the obvious with at and dot -- Regards Roger Govier "LiveUser" wrote in message ... Roger, I do appreciate your help. I wonder if it has anything to do with Graphs in the workbook. I have graphs based on information in the workbook. It seems like it should work, I just don't know what is going on. But, Thank you for your assistance. "Roger Govier" wrote: Hi I'm sorry, but I cannot answer your problem. If your code works for a single sheet, then it SHOULD work when cycling through each other sheet within the Workbook. Perhaps someone else has an answer for this. -- Regards Roger Govier "LiveUser" wrote in message ... Roger, It could be any cell that is selected. I used A1 as an example. Thank you. "Roger Govier" wrote: Hi If it is A1 on each sheet that is wanted, then after the line ws.activate Range("A1").Select -- Regards Roger Govier "LiveUser" wrote in message ... With the code I just posted: I select a cell (A1). Inside the cell is the word yellow. I start the macro and I get a popup window - "Enter Search Column" - which shows (A) being the selected column. I click OK. I get a popup window - "Enter Search String" - Nothing shows up, like it does without your additional code, so I manually enter in the information from (A1) - Yellow. I click OK I get a popup window - "Do you really want to delete rows with empty cells?" - what is automatically entered is "No". I click OK. Nothing happens. Cell (A1) is still there and no rows throughout the workbook have been deleted. The macro has stopped. Thank you. "Roger Govier" wrote: Hi In what way does it not work? What error message do you get? If it worked for your individual sheet, what is different about your other sheets? The only difference to the original code you posted, is making the macro loop through each worksheet in the workbook in turn, then running your macro. -- Regards Roger Govier "LiveUser" wrote in message ... I still couldn't get it to work. Here is what I have: Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Activate 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error GoTo 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck < "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress < C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Next Application.ScreenUpdating = True End Sub "Roger Govier" wrote: Hi I shouldn't have include the line Range("E1").Select -- Regards Roger Govier "LiveUser" wrote in message ... smw226 via OfficeKB.com and Roger Govier, I didn't have any luck with the code you gave me. I tried moving it around a bit also, but couldn't get it to work. I don't know what I could be doing wrong. "LiveUser" wrote: What do I need to do to this VBA code so when I start it it ap |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Code to find code | Excel Discussion (Misc queries) | |||
Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
Unprotect Code Module in Code | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) |