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

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

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

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

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

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

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

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

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

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

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

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

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