Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
Hi everyone. I have a col that contains many other text such as: A1 - abc A2 - abc A3 -abc A4 - asdkalk A5 -asdlakjd A6 -asdkaj A7 -anything |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
Hello, I can help you if what you need is: if cell doesn't equal 'abc' then delete what is in the cell. It would then require you to sort afterwards so that you don't have loads of empty rows. the code is Sub subname() Dim cell As Range For Each cell In Range("A:A") If cell.Value < "abc" Then cell.Value = "" Else End If Next End Sub --- if you want to delete the contents of the entire row then replace cell.value = "" with 'cell.EntireRow.ClearContents' Hope this helps Thanks John -- johncassell ------------------------------------------------------------------------ johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016 View this thread: http://www.excelforum.com/showthread...hreadid=565629 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
Hello, I can help you if what you need is: if cell doesn't equal 'abc' then delete what is in the cell. It would then require you to sort afterwards so that you don't have loads of empty rows. the code is Sub subname() Dim cell As Range For Each cell In Range("A:A") If cell.Value < "abc" Then cell.Value = "" Else End If Next End Sub --- if you want to delete the contents of the entire row then replace cell.value = "" with 'cell.EntireRow.ClearContents' Hope this helps Thanks John -- johncassell ------------------------------------------------------------------------ johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016 View this thread: http://www.excelforum.com/showthread...hreadid=565629 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
Hello, I can help you if what you need is: if cell doesn't equal 'abc' then delete what is in the cell. It would then require you to sort afterwards so that you don't have loads of empty rows. the code is Sub subname() Dim cell As Range For Each cell In Range("A:A") If cell.Value < "abc" Then cell.Value = "" Else End If Next End Sub --- if you want to delete the contents of the entire row then replace cell.value = "" with 'cell.EntireRow.ClearContents' also, this code takes a while to run because it looks at every cell in column A so you could implement a code which will stop the macro if the next cell it looks in is empty.. --- Sub subname() Dim cell As Range For Each cell In Range("A:A") If cell.Value = "" Then End Else If cell.Value < "abc" Then cell.EntireRow.ClearContents Else End If End If Next End Sub --- Hope this helps Thanks John -- johncassell ------------------------------------------------------------------------ johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016 View this thread: http://www.excelforum.com/showthread...hreadid=565629 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
great, very thanks but i aslo get 1 little question more :) 1) your code delete OK, but deleted rows is not up. It mean it keep orgianal position. 2) and now, pls help me, i wanna del from rows 2 to end of rows in database because the first row is title, man, :)) thank you very much for help -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=565629 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
Hi, Yes this is the part that I cannot do. It is possible to find the cell which doesn't equal 'abc' and the delete it (i.e. move the cell up but then it will not look in the ver next cell. What I mean is.. if you have the following information: cell A1 = Title A2 = abc A3 = abc A4 = ggggg A5 = ffffff A6 = abc and then using your code, replace 'cell.Value = ""' with cell.entirerow.Delete Shift:=xlUp this will find that cell A4 doesn't equal 'abc' and delete the row an shift the row up one. The problem is that the loop will check cell A5 next but A5 will no equal 'abc' because it was moved up the column so it won't have foun ffffff. Does this make sense? Joh -- johncassel ----------------------------------------------------------------------- johncassell's Profile: http://www.excelforum.com/member.php...fo&userid=2501 View this thread: http://www.excelforum.com/showthread.php?threadid=56562 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
i have a code to del all zero as following: Sub Del_zero() findstring = "0" Set B = Range("L:L").Find(What:=findstring, LookAt:=xlWhole) While Not (B Is Nothing) B.EntireRow.Delete Set B = Range("L:L").Find(What:=findstring, LookAt:=xlWhole) Wend End Sub it's work great. now , i share with you, and thinking about my problem with keep "text" above ? thank you -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=565629 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
i have a code to del all zero as following: Sub Del_zero() findstring = "0" Set B = Range("L:L").Find(What:=findstring, LookAt:=xlWhole) While Not (B Is Nothing) B.EntireRow.Delete Set B = Range("L:L").Find(What:=findstring, LookAt:=xlWhole) Wend End Sub it's work great. now , i share with you, and thinking about my problem with keep "text" above ? thank you -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=565629 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
using your code with mine (your code was very helpful to me by the way, thank you :-)) this will look in all cells from cell A2 (so it ignores the title cell) and change the value of the cell to 'delete' if it does not equal 'abc'. when this is finshed it will then run your code which deletes the row and shifts it up if the value equals 'delete' ---Sub subname() Dim cell As Range For Each cell In Range("A2:A65536") If cell.Value = "" Then Run ("Del_zero") End Else If cell.Value < "abc" Then cell.Value = "delete" Else End If End If Next End Sub Sub Del_zero() findstring = "delete" Set B = Range("A:A").Find(What:=findstring, LookAt:=xlWhole) While Not (B Is Nothing) B.EntireRow.Delete Set B = Range("A:A").Find(What:=findstring, LookAt:=xlWhole) Wend End Sub --- Thanks John -- johncassell ------------------------------------------------------------------------ johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016 View this thread: http://www.excelforum.com/showthread...hreadid=565629 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
hi man, no work man, how to integrate both into one function and work great. thank yo -- vumia ----------------------------------------------------------------------- vumian's Profile: http://www.excelforum.com/member.php...fo&userid=3649 View this thread: http://www.excelforum.com/showthread.php?threadid=56562 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
hi man, do you know this, help me pls i've got this function to check another file opened ? Public Function gbIsWBOpen(rsName As String) As Boolean On Error Resume Next gbIsWBOpen = Len(Workbooks(rsName).Name) On Error GoTo 0 End Function sub check_open if gbIsWBOpen("abc.xls").active = true then 'do function else msgbox "error" end if end sub what's wrong with this man ? thank you. -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=565629 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
hi man, do you know this, help me pls i've got this function to check another file opened ? Public Function gbIsWBOpen(rsName As String) As Boolean On Error Resume Next gbIsWBOpen = Len(Workbooks(rsName).Name) On Error GoTo 0 End Function sub check_open if gbIsWBOpen("abc.xls").active = true then 'do function else msgbox "error" end if end sub what's wrong with this man ? thank you. -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=565629 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
For other examples see
http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "vumian" wrote in message ... hi man, no work man, how to integrate both into one function and work great. thank you -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=565629 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
Look at the example in my reply in your other thread
You can test if a file is open with this function Function bIsBookOpen(ByRef szBookName As String) As Boolean ' Rob Bovey On Error Resume Next bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing) End Function Use like this If bIsBookOpen("test.xls") Then -- Regards Ron de Bruin http://www.rondebruin.nl "vumian" wrote in message ... hi man, do you know this, help me pls i've got this function to check another file opened ? Public Function gbIsWBOpen(rsName As String) As Boolean On Error Resume Next gbIsWBOpen = Len(Workbooks(rsName).Name) On Error GoTo 0 End Function sub check_open if gbIsWBOpen("abc.xls").active = true then 'do function else msgbox "error" end if end sub what's wrong with this man ? thank you. -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=565629 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete these rows ?
Sub checkisopen() On Error Resume Next If Application.Workbooks("abc.xls") Is Nothing Then MsgBox ("abc.xls is not open") Else MsgBox ("abc.xls is open") End If End Sub thanks Joh -- johncassel ----------------------------------------------------------------------- johncassell's Profile: http://www.excelforum.com/member.php...fo&userid=2501 View this thread: http://www.excelforum.com/showthread.php?threadid=56562 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Delete rows with numeric values, leave rows with text | Excel Programming | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |