Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
Hi all, Trying to loop through entire woorksheet and then based on cell criteria (empty or not) delete the entire row. I have the following implemented....but dows not work; Code: -------------------- Private Sub cmdFilter_Click() 'Copy Data Sheet & Paste Data onto a New Sheet Cells.Select Selection.Copy Worksheets.Add ActiveSheet.Paste ActiveSheet.Rows(1).Select Selection.Delete 'Delete rows that are not needed strMySheet = ActiveSheet.Name Sheets(strMySheet).Select Sheets(strMySheet).Cells(1, 1).Select i = 1 Do If (Cells(i, 4).Value = "") And (Cells(i, 5).Value < "") Then Sheets(strMySheet).Cells(i, 1).Select Selection.EntireRow.Delete End If i = i + 1 Loop Until (Sheets(strMySheet).Cells(i, 1).Value < "") End Sub -------------------- Any tips or feedback welcome! Thanks -- gti_jobert ------------------------------------------------------------------------ gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634 View this thread: http://www.excelforum.com/showthread...hreadid=517729 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
Hi GTI,
Try: '============= Public Sub Tester() On Error Resume Next ActiveSheet.Columns(1). _ SpecialCells(xlBlanks).EntireRow.Delete On Error GoTo 0 End Sub '<<============= --- Regards, Norman "gti_jobert" wrote in message ... Hi all, Trying to loop through entire woorksheet and then based on cell criteria (empty or not) delete the entire row. I have the following implemented....but dows not work; Code: -------------------- Private Sub cmdFilter_Click() 'Copy Data Sheet & Paste Data onto a New Sheet Cells.Select Selection.Copy Worksheets.Add ActiveSheet.Paste ActiveSheet.Rows(1).Select Selection.Delete 'Delete rows that are not needed strMySheet = ActiveSheet.Name Sheets(strMySheet).Select Sheets(strMySheet).Cells(1, 1).Select i = 1 Do If (Cells(i, 4).Value = "") And (Cells(i, 5).Value < "") Then Sheets(strMySheet).Cells(i, 1).Select Selection.EntireRow.Delete End If i = i + 1 Loop Until (Sheets(strMySheet).Cells(i, 1).Value < "") End Sub -------------------- Any tips or feedback welcome! Thanks -- gti_jobert ------------------------------------------------------------------------ gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634 View this thread: http://www.excelforum.com/showthread...hreadid=517729 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
Hi, that would work, but i need to base it other cirteria as well - no just the cells i suggested in my first post -- gti_jober ----------------------------------------------------------------------- gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063 View this thread: http://www.excelforum.com/showthread.php?threadid=51772 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
Then give all the details.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "gti_jobert" wrote in message ... Hi, that would work, but i need to base it other cirteria as well - not just the cells i suggested in my first post. -- gti_jobert ------------------------------------------------------------------------ gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634 View this thread: http://www.excelforum.com/showthread...hreadid=517729 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
Hello, I have a sheet containing masses of data. I want all rows to be deleted IF (Col 4 isnull And Col 5 isNotNull And Col 9 isnull And Col 10 isNotNull) I hope this helps.....I have been trying a few variations of code but dont seem to understand what its doing; Code: -------------------- i = 2 Do ActiveSheet.Rows(i).Select If Cells(i, 4).Value = "" Then ActiveSheet.Rows(i - 1).Select MsgBox Cells(i, 4).Value Selection.Delete End If i = i + 1 Loop Until i = 40 -------------------- The above is just an example based on one Col criteria - and doesn't even delete those rows -- gti_jobert ------------------------------------------------------------------------ gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634 View this thread: http://www.excelforum.com/showthread...hreadid=517729 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
I think I have sorted it now bob...did the following; Code: -------------------- i = 1 Do If ActiveSheet.Cells(i, 4).Value = "" Then ActiveSheet.Rows(i).Select 'MsgBox ActiveSheet.Cells(i, 1).Value Selection.Delete i = i - 1 End If i = i + 1 Loop Until ActiveSheet.Cells(i, 1).Value = "" -------------------- -- gti_jobert ------------------------------------------------------------------------ gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634 View this thread: http://www.excelforum.com/showthread...hreadid=517729 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
Hi all,
I have no experience whatsoever with VBA. Can you please tell me what will be the code to delete all rows which do not have numerical values in the cells in first column (the ones I want to get rid of are either empty or contain text)? Thank you, emil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
Help!!! I have problem deleting 2500 rows of filtered rows | Excel Programming | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions |