Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Delete the last N rows | Excel Discussion (Misc queries) | |||
Macro to delete rows if... | Excel Discussion (Misc queries) | |||
Need macro to delete all text cells in an Excel range | Excel Worksheet Functions | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming |