Macro to delete rows with text cells
 

Hi all,

Can anyone help me with changing the vba code below to delete each ro
with a text field in column c1:c10000? I am new to vba and just manag
to hack something up. However this code only deletes blank cells an
not cells with text.

Also is there a better way to specify the column range instead of doin
it by specifying the range like this...Set Rng
ActiveSheet.Range("C1:C10000"). i.e. Is there a way to make it dynamic
so regardless of how many rows there are, it will run through each an
every row with data in it. The problem that I faced is my data has th
occasionaly blank row between each row with data.

Anyway, enough said. Here is my code. Thanks in advance.

Sub DeleteText()
'
' Delete blank lines in column C
' Macro recorded 23/08/2004 by zsalleh
'

'
Windows("Workbook v2.xls").Activate
Sheets("Consolidation").Select
Application.ScreenUpdating = False
Dim Rng As Range
Set Rng = ActiveSheet.Range("C1:C10000")

For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "
Then
Rng.Item(ix).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True

End Su

--
Message posted from http://www.ExcelForum.com

Macro to delete rows with text cells
 

Try this one but you need to specify the selected area by highlighting the
first to the end cell.

Sub DeleteEmptyCell()
Dim Rng As Range
For Each Rng In Selection
If Rng.Value = "" Then Rng.EntireRow.Delete
Next
End Sub



"zsalleh " ¼¶¼g©ó¶l¥ó
...
Hi all,

Can anyone help me with changing the vba code below to delete each row
with a text field in column c1:c10000? I am new to vba and just manage
to hack something up. However this code only deletes blank cells and
not cells with text.

Also is there a better way to specify the column range instead of doing
it by specifying the range like this...Set Rng =
ActiveSheet.Range("C1:C10000"). i.e. Is there a way to make it dynamic,
so regardless of how many rows there are, it will run through each and
every row with data in it. The problem that I faced is my data has the
occasionaly blank row between each row with data.

Anyway, enough said. Here is my code. Thanks in advance.

Sub DeleteText()
'
' Delete blank lines in column C
' Macro recorded 23/08/2004 by zsalleh
'

'
Windows("Workbook v2.xls").Activate
Sheets("Consolidation").Select
Application.ScreenUpdating = False
Dim Rng As Range
Set Rng = ActiveSheet.Range("C1:C10000")

For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = ""
Then
Rng.Item(ix).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True

End Sub


---
Message posted from http://www.ExcelForum.com/

Macro to delete rows with text cells
 

Try something like

For ix = Rng.Count To 1 Step -1
If Not IsNumeric(.Item(ix).Value) And Not .Item(ix).HasFormula Then
Rng.Item(ix).EntireRow.Delete
End If
Next


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"zsalleh " wrote in message
...
Hi all,

Can anyone help me with changing the vba code below to delete each row
with a text field in column c1:c10000? I am new to vba and just manage
to hack something up. However this code only deletes blank cells and
not cells with text.

Also is there a better way to specify the column range instead of doing
it by specifying the range like this...Set Rng =
ActiveSheet.Range("C1:C10000"). i.e. Is there a way to make it dynamic,
so regardless of how many rows there are, it will run through each and
every row with data in it. The problem that I faced is my data has the
occasionaly blank row between each row with data.

Anyway, enough said. Here is my code. Thanks in advance.

Sub DeleteText()
'
' Delete blank lines in column C
' Macro recorded 23/08/2004 by zsalleh
'

'
Windows("Workbook v2.xls").Activate
Sheets("Consolidation").Select
Application.ScreenUpdating = False
Dim Rng As Range
Set Rng = ActiveSheet.Range("C1:C10000")

For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = ""
Then
Rng.Item(ix).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True

End Sub


---
Message posted from http://www.ExcelForum.com/

Macro to delete rows with text cells
 

Hi Zsalleh .

As an alternative, try:

Sub Tester()
Application.ScreenUpdating = False
On Error Resume Next
Columns("C:C").SpecialCells(xlCellTypeConstants, 2) _
.EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub


---
Regards,
Norman



"zsalleh " wrote in message
...
Hi all,

Can anyone help me with changing the vba code below to delete each row
with a text field in column c1:c10000? I am new to vba and just manage
to hack something up. However this code only deletes blank cells and
not cells with text.

Also is there a better way to specify the column range instead of doing
it by specifying the range like this...Set Rng =
ActiveSheet.Range("C1:C10000"). i.e. Is there a way to make it dynamic,
so regardless of how many rows there are, it will run through each and
every row with data in it. The problem that I faced is my data has the
occasionaly blank row between each row with data.

Anyway, enough said. Here is my code. Thanks in advance.

Sub DeleteText()
'
' Delete blank lines in column C
' Macro recorded 23/08/2004 by zsalleh
'

'
Windows("Workbook v2.xls").Activate
Sheets("Consolidation").Select
Application.ScreenUpdating = False
Dim Rng As Range
Set Rng = ActiveSheet.Range("C1:C10000")

For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = ""
Then
Rng.Item(ix).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True

End Sub


---
Message posted from http://www.ExcelForum.com/

Macro to delete rows with text cells
 

Norman:
xlCellTypeConstants << is this equivalent to text?
Tks,
Jim May

"Norman Jones" wrote in message
...
Hi Zsalleh .

As an alternative, try:

Sub Tester()
Application.ScreenUpdating = False
On Error Resume Next
Columns("C:C").SpecialCells(xlCellTypeConstants, 2) _
.EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub


---
Regards,
Norman



"zsalleh " wrote in message
...
Hi all,

Can anyone help me with changing the vba code below to delete each row
with a text field in column c1:c10000? I am new to vba and just manage
to hack something up. However this code only deletes blank cells and
not cells with text.

Also is there a better way to specify the column range instead of doing
it by specifying the range like this...Set Rng =
ActiveSheet.Range("C1:C10000"). i.e. Is there a way to make it dynamic,
so regardless of how many rows there are, it will run through each and
every row with data in it. The problem that I faced is my data has the
occasionaly blank row between each row with data.

Anyway, enough said. Here is my code. Thanks in advance.

Sub DeleteText()
'
' Delete blank lines in column C
' Macro recorded 23/08/2004 by zsalleh
'

'
Windows("Workbook v2.xls").Activate
Sheets("Consolidation").Select
Application.ScreenUpdating = False
Dim Rng As Range
Set Rng = ActiveSheet.Range("C1:C10000")

For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = ""
Then
Rng.Item(ix).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True

End Sub


---
Message posted from http://www.ExcelForum.com/

Macro to delete rows with text cells
 

Hi Jim,

In expression:

SpecialCells(xlCellTypeConstants, 2)

the value 2 corresponds to the argument:

xlTextValues

Hence the expression, in its entirety, defines constant text values.


---
Regards,
Norman



"Jim May" wrote in message
news:zrkXc.24120$L94.4894@fed1read07...
Norman:
xlCellTypeConstants << is this equivalent to text?
Tks,
Jim May

"Norman Jones" wrote in message
...
Hi Zsalleh .

As an alternative, try:

Sub Tester()
Application.ScreenUpdating = False
On Error Resume Next
Columns("C:C").SpecialCells(xlCellTypeConstants, 2) _
.EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub


---
Regards,
Norman



"zsalleh " wrote in message
...
Hi all,

Can anyone help me with changing the vba code below to delete each row
with a text field in column c1:c10000? I am new to vba and just manage
to hack something up. However this code only deletes blank cells and
not cells with text.

Also is there a better way to specify the column range instead of
doing
it by specifying the range like this...Set Rng =
ActiveSheet.Range("C1:C10000"). i.e. Is there a way to make it
dynamic,
so regardless of how many rows there are, it will run through each and
every row with data in it. The problem that I faced is my data has the
occasionaly blank row between each row with data.

Anyway, enough said. Here is my code. Thanks in advance.

Sub DeleteText()
'
' Delete blank lines in column C
' Macro recorded 23/08/2004 by zsalleh
'

'
Windows("Workbook v2.xls").Activate
Sheets("Consolidation").Select
Application.ScreenUpdating = False
Dim Rng As Range
Set Rng = ActiveSheet.Range("C1:C10000")

For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = ""
Then
Rng.Item(ix).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True

End Sub


---
Message posted from http://www.ExcelForum.com/

Macro to delete rows with text cells
 

Jim,

The ,2 says that it is a text type constant.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jim May" wrote in message
news:zrkXc.24120$L94.4894@fed1read07...
Norman:
xlCellTypeConstants << is this equivalent to text?
Tks,
Jim May

"Norman Jones" wrote in message
...
Hi Zsalleh .

As an alternative, try:

Sub Tester()
Application.ScreenUpdating = False
On Error Resume Next
Columns("C:C").SpecialCells(xlCellTypeConstants, 2) _
.EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub


---
Regards,
Norman



"zsalleh " wrote in message
...
Hi all,

Can anyone help me with changing the vba code below to delete each row
with a text field in column c1:c10000? I am new to vba and just manage
to hack something up. However this code only deletes blank cells and
not cells with text.

Also is there a better way to specify the column range instead of
doing
it by specifying the range like this...Set Rng =
ActiveSheet.Range("C1:C10000"). i.e. Is there a way to make it
dynamic,
so regardless of how many rows there are, it will run through each and
every row with data in it. The problem that I faced is my data has the
occasionaly blank row between each row with data.

Anyway, enough said. Here is my code. Thanks in advance.

Sub DeleteText()
'
' Delete blank lines in column C
' Macro recorded 23/08/2004 by zsalleh
'

'
Windows("Workbook v2.xls").Activate
Sheets("Consolidation").Select
Application.ScreenUpdating = False
Dim Rng As Range
Set Rng = ActiveSheet.Range("C1:C10000")

For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = ""
Then
Rng.Item(ix).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True

End Sub


---
Message posted from http://www.ExcelForum.com/

Macro to delete rows with text cells
 

Norman and Bob:
appreciate your help,,,,,
JMay

"Bob Phillips" wrote in message
...
Jim,

The ,2 says that it is a text type constant.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jim May" wrote in message
news:zrkXc.24120$L94.4894@fed1read07...
Norman:
xlCellTypeConstants << is this equivalent to text?
Tks,
Jim May

"Norman Jones" wrote in message
...
Hi Zsalleh .

As an alternative, try:

Sub Tester()
Application.ScreenUpdating = False
On Error Resume Next
Columns("C:C").SpecialCells(xlCellTypeConstants, 2) _
.EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub


---
Regards,
Norman



"zsalleh " wrote in message
...
Hi all,

Can anyone help me with changing the vba code below to delete each
row
with a text field in column c1:c10000? I am new to vba and just
manage
to hack something up. However this code only deletes blank cells and
not cells with text.

Also is there a better way to specify the column range instead of
doing
it by specifying the range like this...Set Rng =
ActiveSheet.Range("C1:C10000"). i.e. Is there a way to make it
dynamic,
so regardless of how many rows there are, it will run through each
and
every row with data in it. The problem that I faced is my data has
the
occasionaly blank row between each row with data.

Anyway, enough said. Here is my code. Thanks in advance.

Sub DeleteText()
'
' Delete blank lines in column C
' Macro recorded 23/08/2004 by zsalleh
'

'
Windows("Workbook v2.xls").Activate
Sheets("Consolidation").Select
Application.ScreenUpdating = False
Dim Rng As Range
Set Rng = ActiveSheet.Range("C1:C10000")

For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = ""
Then
Rng.Item(ix).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True

End Sub


---
Message posted from http://www.ExcelForum.com/

Macro to delete rows with text cells
 

As ever, it is a pleasure Jim.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message
news:WktXc.161841$Oi.6833@fed1read04...
Norman and Bob:
appreciate your help,,,,,
JMay

"Bob Phillips" wrote in message
...
Jim,

The ,2 says that it is a text type constant.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jim May" wrote in message
news:zrkXc.24120$L94.4894@fed1read07...
Norman:
xlCellTypeConstants << is this equivalent to text?
Tks,
Jim May

"Norman Jones" wrote in message
...
Hi Zsalleh .

As an alternative, try:

Sub Tester()
Application.ScreenUpdating = False
On Error Resume Next
Columns("C:C").SpecialCells(xlCellTypeConstants, 2) _
.EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub


---
Regards,
Norman



"zsalleh " wrote in message
...
Hi all,

Can anyone help me with changing the vba code below to delete each
row
with a text field in column c1:c10000? I am new to vba and just
manage
to hack something up. However this code only deletes blank cells
and
not cells with text.

Also is there a better way to specify the column range instead of
doing
it by specifying the range like this...Set Rng =
ActiveSheet.Range("C1:C10000"). i.e. Is there a way to make it
dynamic,
so regardless of how many rows there are, it will run through each
and
every row with data in it. The problem that I faced is my data has
the
occasionaly blank row between each row with data.

Anyway, enough said. Here is my code. Thanks in advance.

Sub DeleteText()
'
' Delete blank lines in column C
' Macro recorded 23/08/2004 by zsalleh
'

'
Windows("Workbook v2.xls").Activate
Sheets("Consolidation").Select
Application.ScreenUpdating = False
Dim Rng As Range
Set Rng = ActiveSheet.Range("C1:C10000")

For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = ""
Then
Rng.Item(ix).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True

End Sub


---
Message posted from http://www.ExcelForum.com/