Re: 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 *** that is wanted, then after the line
ws.activate
Range("A1").Select

--

Regards
Roger Govier

"LiveUser" <LiveUser@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F2D9099A-C928-4054-8521-1BC9E64AF752@xxxxxxxxxxxxxxxx
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 ***, what is different about your
other
sheets?

The only difference to the original code you posted, is making the macro
loop through each work*** in the workbook in turn, then running your
macro.

--

Regards
Roger Govier

"LiveUser" <LiveUser@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AB0509BC-10A6-42D9-9D99-E313ABD882F9@xxxxxxxxxxxxxxxx
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 Work***
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" <LiveUser@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B3D27F4F-1F46-44F7-A95C-9B6C8E64E1EE@xxxxxxxxxxxxxxxx
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 work***?

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




.