Macro to delete unnecessary rows and sort the rest

mac_see

Active Member
Joined
Oct 15, 2002
Messages
419
Book3
ABCDEFGHIJKL
1A10.00%C180.00%C86.96
22B375.00%B13.04
33B225.00%
4B113.04%C315.00%
5225.00%C25.00%
6375.00%
7C186.96%80.00%
825.00%
9315.00%
10
Sheet1


I need a macro that can eliminate rows where figures in column D are blank and then sort the rest of the rows based on column D and put the result in column F:H

Similarly, eliminate rows where figures in column C are blank and then sort the rest of the rows based on column C and put the result in coumn J:K

There are merged cells which I cannot unmerge. The above is just an example, If I read and understand the logic behind the macro, I can manipulate it and use it for my data

Thanx
Maxi
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Maxi

Hopefully this will get you going.

Code:
Sub bbb()
 lastrow = Cells(Rows.Count, "B").End(xlUp).Row
 Range("A1:A" & lastrow).SpecialCells(xlCellTypeBlanks).Formula = "=A1"
 Range("A1:A" & lastrow).Value = Range("a1:a" & lastrow).Value
 Range("D1:D" & lastrow).Copy Destination:=Range("H1")
 Range("H1:H" & lastrow).Sort key1:=Range("H1"), order1:=xlDescending, header:=xlNo
 For Each ce In Range("H1").CurrentRegion
  ce.Offset(0, -1).Value = Evaluate("=Index(B:B,Match(" & ce.Value & ", D:D, 0))")
  ce.Offset(0, -2).Value = Evaluate("=Index(A:A,Match(" & ce.Value & ", D:D, 0))")
 Next ce

 Range("C1:C" & lastrow).Copy Destination:=Range("K1")
 Range("K1:K" & lastrow).Sort key1:=Range("K1"), order1:=xlDescending, header:=xlNo
 For Each ce In Range("K1").CurrentRegion
  ce.Offset(0, -1).Value = Evaluate("=Index(A:A,Match(" & ce.Value & ", C:C, 0))")
 Next ce
End Sub

Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,118,910
Messages
5,574,987
Members
412,632
Latest member
robertmwaring2
Top