Macro to delete rows with text cells

[zsalleh]

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

 

[Eddy]

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

 

[Bob Phillips]

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)

 

[Norman Jones]

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

 

[Jim May]

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

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

 

[Norman Jones]

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

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

 

[Bob Phillips]

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)

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

 

[JMay]

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

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)

 

[Bob Phillips]

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)