Previous Topic Next Topic

Macro to delete rows with certain values

Closed
LK - Feb 18, 2011 at 09:28 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 27, 2011 at 04:03 PM
Hello,

I am trying to delete rows that have specific values using a macro my problem is that I have several values that would mean that row needs to be deleted. They are simple ones such as 19 or 35 or 52 or 66 or 104 (or a whole bunch more numbers) in column A. Everytime I try and create a macro it deletes that row number based on the row number not the value in the cell in Column A.

Thanks for the assistance.

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 18, 2011 at 11:19 AM
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.


Note: your data need not be to be real data but a good representative of how data looks like

Put your MACRO too in the sample book
0
Answer 2 / 2
LK
Feb 18, 2011 at 12:50 PM
Here is an example of what I am trying to get it to look like in the end:
http://spreadsheets.google.com/...

This is what it starts as:
http://spreadsheets.google.com/...

The changes I have made are fixing the column widths, deleteing specific driver numbers, removing the background fill colour (Ideally I want to highlight drivers 612 605 621 14 121 105 624), and sorting it in descending order based on column D (payment amounts), and deleting the average payment column.

Most of it I can do I am just having a lot of trouble getting it to delete the drivers I don't want. Everytime I try to create the macro it deletes it based on the row number not the driver number. This doesn't work for me because some days not all the drivers are there so it ends up deleting the wrong rows.

Below are the two macros I have tried that have not been successful.
Sub DriverPay2()

'

' DriverPay2 Macro

' Macro recorded 2/17/2011 by lckendrick

'

' Keyboard Shortcut: Ctrl+Shift+P

'

Columns("A:A").ColumnWidth = 6.14

Columns("B:B").ColumnWidth = 22.86

Columns("B:B").ColumnWidth = 20.57

ActiveWindow.SmallScroll Down:=-6

Columns("C:C").EntireColumn.AutoFit

Columns("E:E").Select

Selection.Delete Shift:=xlToLeft

Range("A2:D186").Select

Selection.Interior.ColorIndex = xlNone

Range("6:6,9:9,10:10,12:12,17:17,21:21,23:23,28:28,31:31").Select

Range("A31").Activate

ActiveWindow.SmallScroll Down:=27

Range( _ "6:6,9:9,10:10,12:12,17:17,21:21,23:23,28:28,31:31,32:32,33:33,42:42,44:44,45:45,46:46,47:47,49:49,50:50,51:51,52:52,53:53" _

).Select

Range("A53").Activate

ActiveWindow.SmallScroll Down:=15

Range( _

"6:6,9:9,10:10,12:12,17:17,21:21,23:23,28:28,31:31,32:32,33:33,42:42,44:44,45:45,46:46,47:47,49:49,50:50,51:51,52:52,53:53,55:55,58:58,61:61,62:62,67:67,69:69,70:70,75:75" _

).Select

Range("A75").Activate

ActiveWindow.SmallScroll Down:=24

Union(Range( _

"91:91,92:92,93:93,94:94,6:6,9:9,10:10,12:12,17:17,21:21,23:23,28:28,31:31,32:32,33:33,42:42,44:44,45:45,46:46,47:47,49:49,50:50,51:51,52:52,53:53,55:55,58:58,61:61,62:62,67:67,69:69,70:70" _

), Range("75:75,88:88,89:89,90:90")).Select

Range("A94").Activate

ActiveWindow.SmallScroll Down:=18

Union(Range( _

"91:91,92:92,93:93,94:94,102:102,103:103,104:104,105:105,107:107,6:6,9:9,10:10,12:12,17:17,21:21,23:23,28:28,31:31,32:32,33:33,42:42,44:44,45:45,46:46,47:47,49:49,50:50,51:51,52:52,53:53,55:55,58:58" _

), Range("61:61,62:62,67:67,69:69,70:70,75:75,88:88,89:89,90:90")).Select

Range("A107").Activate

ActiveWindow.SmallScroll Down:=9

Union(Range(

"91:91,92:92,93:93,94:94,102:102,103:103,104:104,105:105,107:107,111:111,116:116,117:117,118:118,119:119,120:120,121:121,122:122,123:123,6:6,9:9,10:10,12:12,17:17,21:21,23:23,28:28,31:31,32:32,33:33,42:42,44:44,45:45" _

), Range(

"46:46,47:47,49:49,50:50,51:51,52:52,53:53,55:55,58:58,61:61,62:62,67:67,69:69,70:70,75:75,88:88,89:89,90:90" _

)).Select

Range("A123").Activate

Selection.Delete Shift:=xlUp

ActiveWindow.SmallScroll Down:=-111

Range("A4:D73").Select

Range("D4").Activate

ActiveWindow.SmallScroll Down:=-30

Rows("33:33").EntireRow.AutoFit

Selection.Sort Key1:=Range("D4"), Order1:=xlDescending, Header:=xlGuess,

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,

DataOption1:=xlSortNormal

Range("E23").Select

ActiveWindow.SmallScroll Down:=24

Range("D54:D57").Select

Range("D57").Activate

ActiveWindow.SmallScroll Down:=27

Rows("70:73").Select

Selection.Delete Shift:=xlUp

ActiveWindow.SmallScroll Down:=-12

Range("A49:D69").Select

Range("D69").Activate

With Selection.Interior

.ColorIndex = 45

.Pattern = xlSolid

End With

Range("G60").Select

End Sub



Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/17/2011 by lckendrick
'
' Keyboard Shortcut: Ctrl+Shift+P
'
ActiveWindow.SmallScroll Down:=-12
Range("A1:D127").Select
Selection.Interior.ColorIndex = xlNone
Range("5:5,8:8,9:9,11:11,16:16").Select
Range("A16").Activate
ActiveWindow.SmallScroll Down:=15
Range("5:5,8:8,9:9,11:11,16:16,20:20,22:22,27:27").Select
Range("A27").Activate
ActiveWindow.SmallScroll Down:=3
Range("5:5,8:8,9:9,11:11,16:16,20:20,22:22,27:27,30:30,32:32,33:33").Select
Range("A33").Activate
ActiveWindow.SmallScroll Down:=12
Range( _
"5:5,8:8,9:9,11:11,16:16,20:20,22:22,27:27,30:30,32:32,33:33,43:43,45:45,46:46,47:47,48:48" _
).Select
Range("A48").Activate
ActiveWindow.SmallScroll Down:=15
Range( _
"5:5,8:8,9:9,11:11,16:16,20:20,22:22,27:27,30:30,32:32,33:33,43:43,45:45,46:46,47:47,48:48,50:50,51:51,52:52,53:53,54:54,55:55" _
).Select
Range("A55").Activate
ActiveWindow.SmallScroll Down:=6
Range( _
"5:5,8:8,9:9,11:11,16:16,20:20,22:22,27:27,30:30,32:32,33:33,43:43,45:45,46:46,47:47,48:48,50:50,51:51,52:52,53:53,54:54,55:55,57:57,60:60,63:63,64:64" _
).Select
Range("A64").Activate
ActiveWindow.SmallScroll Down:=6
Range( _
"5:5,8:8,9:9,11:11,16:16,20:20,22:22,27:27,30:30,32:32,33:33,43:43,45:45,46:46,47:47,48:48,50:50,51:51,52:52,53:53,54:54,55:55,57:57,60:60,63:63,64:64,70:70,72:72,73:73" _
).Select
Range("A73").Activate
ActiveWindow.SmallScroll Down:=9
Range( _
"5:5,8:8,9:9,11:11,16:16,20:20,22:22,27:27,30:30,32:32,33:33,43:43,45:45,46:46,47:47,48:48,50:50,51:51,52:52,53:53,54:54,55:55,57:57,60:60,63:63,64:64,70:70,72:72,73:73,78:78" _
).Select
Range("A78").Activate
ActiveWindow.SmallScroll Down:=24
Union(Range( _
"93:93,94:94,95:95,96:96,97:97,5:5,8:8,9:9,11:11,16:16,20:20,22:22,27:27,30:30,32:32,33:33,43:43,45:45,46:46,47:47,48:48,50:50,51:51,52:52,53:53,54:54,55:55,57:57,60:60,63:63,64:64,70:70" _
), Range("72:72,73:73,78:78,91:91,92:92")).Select
Range("A97").Activate
ActiveWindow.SmallScroll Down:=6
Union(Range( _
"93:93,94:94,95:95,96:96,97:97,100:100,101:101,102:102,103:103,104:104,105:105,106:106,107:107,108:108,109:109,111:111,5:5,8:8,9:9,11:11,16:16,20:20,22:22,27:27,30:30,32:32,33:33,43:43,45:45,46:46,47:47,48:48" _
), Range( _
"50:50,51:51,52:52,53:53,54:54,55:55,57:57,60:60,63:63,64:64,70:70,72:72,73:73,78:78,91:91,92:92" _
)).Select
Range("A111").Activate
ActiveWindow.SmallScroll Down:=15
Union(Range( _
"93:93,94:94,95:95,96:96,97:97,100:100,101:101,102:102,103:103,104:104,105:105,106:106,107:107,108:108,109:109,111:111,115:115,120:120,121:121,122:122,123:123,124:124,125:125,126:126,127:127,5:5,8:8,9:9,11:11,16:16,20:20,22:22" _
), Range( _
"27:27,30:30,32:32,33:33,43:43,45:45,46:46,47:47,48:48,50:50,51:51,52:52,53:53,54:54,55:55,57:57,60:60,63:63,64:64,70:70,72:72,73:73,78:78,91:91,92:92" _
)).Select
Range("A127").Activate
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-138
Columns("A:A").ColumnWidth = 6.43
Columns("B:B").ColumnWidth = 15.43
Columns("B:B").ColumnWidth = 19.29
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Range("A3:C70").Select
Range("C3").Activate
Selection.Sort Key1:=Range("C3"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("D15").Select
ActiveWindow.SmallScroll Down:=42
Range("A50:C70").Select
Range("C70").Activate
With Selection.Interior
.ColorIndex = 46
.Pattern = xlSolid
End With
Range("D60").Select



End Sub
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 18, 2011 at 02:18 PM
I was unable to download the file. Could you upload again on speedyshare. have both pre and post status as two different sheets and if you could add the macro in that same file it would be very convinient
thanks
0
LK
Feb 18, 2011 at 02:35 PM
Unfortunately speedyshare is blocked from my work.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 18, 2011 at 03:23 PM
I think you can upload the excel file with macro etc on google too. I have never used it myself, so I am not 100% sure
0
LK
Feb 18, 2011 at 03:27 PM
Here is the link for the macros I was trying.

http://docs.google.com/...
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 27, 2011 at 04:03 PM
sorry I was looking for the raw data and the final data
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses