Macro to Copy, Sort, Past Formats, Ignore Null

M

mackensteff

Board Regular
Joined
Feb 9, 2010
Messages
74
Office Version
  1. 365
Platform
  1. MacOS
Hello,
My goal is to have a macro copy a known range (columns A:L in picture), sort them by the values typed in the yellow boxes in column M, paste the values starting in column N, ignoring null values, and then paste the format to match columns A:L.

I have include a picture of the desired for results as well as data if anyone is willing to try this out.

I appreciate your time!!


Untitled.png



BibLast, FirstNameDivPlSexPlTimeDivisionSchoolGradeScoreTeamPos.TeamPlaceSort 1stBibLast, FirstNameDivPlSexPlTimeDivisionSchoolGradeScoreTeamPos.TeamPlace
137Green, NoahKober, Maia429018:41Boys: JVCedar103564th PlaceSchool44Blake, LukeChastain, Chara6111819:13Boys: JVCanyon View12NS103rd Place
117Coates, BransonBasnett, Marilu439118:41Boys: JVCedar103674th PlaceSort 2nd49Jarvis, PaulTompson, Eugenie479818:53Boys: JVCanyon View10NS83rd Place
204Sandberg, JackMoree, Lashonda449218:41Boys: JVDesert Hills10NS91st PlaceDivision36Valenzuela, EddieBechard, Emerson5210819:07Boys: JVCanyon View10NS93rd Place
111Webster, MicKeffer, Merrill4418:45Girls: VarsityCedar11413rd PlaceSort 3rd117Coates, BransonBasnett, Marilu439118:41Boys: JVCedar103674th Place
205Hendrix, JaronNale, Kesha459318:46Boys: JVDesert Hills10NS101st PlaceLast, First137Green, NoahKober, Maia429018:41Boys: JVCedar103564th Place
292Dansie, PorterMendoza, Juliet469418:46Boys: JVHurricane103755th Place114Prince, AustinGayles, Bret5911619:12Boys: JVCedar10NS84th Place
259Barker, CorbynPelley, Vonnie499518:46Boys: VarsityDixie104938th Place205Hendrix, JaronNale, Kesha459318:46Boys: JVDesert Hills10NS101st Place
260Wells, GeorgePool, Jung509518:46Boys: VarsityDixie125048th Place207Messel, JordanWegman, Edwina5711419:10Boys: JVDesert Hills10NS121st Place
264Wilkinson, TateMcquire, Ronda519718:48Boys: VarsityDixie115158th Place204Sandberg, JackMoree, Lashonda449218:41Boys: JVDesert Hills10NS91st Place
108Taylor, HarleyStates, Sam5518:52Girls: VarsityCedar11523rd Place254Carlile, SpencerDolby, Tonya6211919:15Boys: JVDixie94747th Place
49Jarvis, PaulTompson, Eugenie479818:53Boys: JVCanyon View10NS83rd Place292Dansie, PorterMendoza, Juliet469418:46Boys: JVHurricane103755th Place
36Valenzuela, EddieBechard, Emerson5210819:07Boys: JVCanyon View10NS93rd Place443Hall, HunterPeer, Kristian5811519:10Boys: JVSnow Canyon94518th Place
500Curtis, NathanKratky, Lilliana5310919:07Boys: JVWasatch104136th Place500Curtis, NathanKratky, Lilliana5310919:07Boys: JVWasatch104136th Place
485Patterson, BradenNystrom, Sachiko5411019:08Boys: JVWasatch114246th Place498Davis, McCallFoulds, Randell5511119:08Boys: JVWasatch114356th Place
498Davis, McCallFoulds, Randell5511119:08Boys: JVWasatch114356th Place486Jarvis, CaedenLaureano, Linh5611319:09Boys: JVWasatch94466th Place
505McCullough, BenFregoso, Stepanie5711219:09Boys: VarsityWasatch115776th Place485Patterson, BradenNystrom, Sachiko5411019:08Boys: JVWasatch114246th Place
486Jarvis, CaedenLaureano, Linh5611319:09Boys: JVWasatch94466th Place259Barker, CorbynPelley, Vonnie499518:46Boys: VarsityDixie104938th Place
207Messel, JordanWegman, Edwina5711419:10Boys: JVDesert Hills10NS121st Place260Wells, GeorgePool, Jung509518:46Boys: VarsityDixie125048th Place
443Hall, HunterPeer, Kristian5811519:10Boys: JVSnow Canyon94518th Place264Wilkinson, TateMcquire, Ronda519718:48Boys: VarsityDixie115158th Place
114Prince, AustinGayles, Bret5911619:12Boys: JVCedar10NS84th Place344Krzymowski, TimRamey, Stanley5610719:07Boys: VarsityManti125629th Place
293Poston, TatynGoold, Barb6011719:12Boys: JVHurricane114675th Place505McCullough, BenFregoso, Stepanie5711219:09Boys: VarsityWasatch115776th Place
44Blake, LukeChastain, Chara6111819:13Boys: JVCanyon View12NS103rd Place108Taylor, HarleyStates, Sam5518:52Girls: VarsityCedar11523rd Place
374Finch, JaceyRee, Olen7719:14Girls: VarsityPine View12731st Place111Webster, MicKeffer, Merrill4418:45Girls: VarsityCedar11413rd Place
254Carlile, SpencerDolby, Tonya6211919:15Boys: JVDixie94747th Place374Finch, JaceyRee, Olen7719:14Girls: VarsityPine View12731st Place

<tbody>
</tbody>
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
I don't see any null values in the picture, thus, don't know what to do when there are null values.
 
Last edited:
Upvote 0
I'm not sure there is a way to indicate them in the table, but if a formula returns "", they tend to be sorted to the top as blank rows. However, I can likely work around the null values by selecting only then cells with shown data. Really, any thing at this point would help.

Thanks!!
 
Upvote 0
Which one is the primary sorting key? I thought it was school but looking at the sorted result I think division is more like it.

Anyway, here is the code. You can modify it to suit your need.

Code: 
Sub sorting()

Dim Lrow As Integer 'last non-empty row in column A
Dim rKey1 As Range
Dim rKey2 As Range
Dim rKey3 As Range

'find the last non-empty row in column A
Lrow = Range("A1:L1").Cells(1048576, 1).End(xlUp).Row

'copy column A to L and paste to N1
Range(Cells(1, 1), Cells(Lrow, 12)).Copy Destination:=Range("N1")

'find key1
Set rKey1 = ActiveSheet.Range("N1:Y1").Find(what:=Range("M2").Value, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)

If rKey1 Is Nothing Then
MsgBox ("Cannot find " & Range("M2") & ". Exiting program.")
Exit Sub
End If

'find key2
Set rKey2 = ActiveSheet.Range("N1:Y1").Find(what:=Range("M4").Value, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)

If rKey2 Is Nothing Then
MsgBox ("Cannot find " & Range("M4") & ". Exiting program.")
Exit Sub
End If

'find key3
Set rKey3 = ActiveSheet.Range("N1:Y1").Find(what:=Range("M6").Value, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)

If rKey3 Is Nothing Then
MsgBox ("Cannot find " & Range("M6") & ". Exiting program.")
Exit Sub
End If

'sort by key1, key2, key3, in that order
Range(Cells(1, 14), Cells(Lrow, 25)).Sort _
    Key1:=rKey1, Order1:=xlAscending, _
    Key2:=rKey2, Order2:=xlAscending, _
    Key3:=rKey3, Order3:=xlAscending, _
    Header:=xlYes, dataoption3:=xlSortTextAsNumbers, _
    MatchCase:=False, Orientation:=xlTopToBottom
End Sub
 
Last edited:
Upvote 0
Hello,
You are right that Division was the one I used in the example. Your macro works, so I really appreciate that. However, just two little things if you could help on I would appreciate it. I am copying from cells that have formulas, so is there a way to paste only values and formats and not the formulas themselves. Second, is there a way to paste the formatting after the sorting so the row colors are retained from the original copied section.

Finally, and I know I am pushing your goodness, but some of the Column A cells are blank (this is not shown in the sample), but is it possible to not copy those rows that have a blank, or null value in column A.

Thanks!
 
Upvote 0
Try this. Although I tested it on the example you provided above, which contains no formulae, it should work and not copy formulae.

Code: 
Sub sorting()

Dim Lrow As Double
Dim rKey1 As Range
Dim rKey2 As Range
Dim rKey3 As Range
Dim i As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Lrow = Cells(1048576, 1).End(xlUp).Row

'filter column A to hide nulls
ActiveSheet.Range("$A:$A").AutoFilter Field:=1, Criteria1:="<>"

'copy and paste by value
Range(Cells(1, 1), Cells(Lrow, 12)).Copy
Range("N1").PasteSpecial (-4163)

'look for sorting key's position
Set rKey1 = ActiveSheet.Range("N1:Y1").Find(what:=Range("M2").Value, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)

If rKey1 Is Nothing Then
MsgBox ("Cannot find " & Range("M2") & ". Exiting program.")
Exit Sub
End If

Set rKey2 = ActiveSheet.Range("N1:Y1").Find(what:=Range("M4").Value, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)

If rKey2 Is Nothing Then
MsgBox ("Cannot find " & Range("M4") & ". Exiting program.")
Exit Sub
End If

Set rKey3 = ActiveSheet.Range("N1:Y1").Find(what:=Range("M6").Value, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)

If rKey3 Is Nothing Then
MsgBox ("Cannot find " & Range("M6") & ". Exiting program.")
Exit Sub
End If

'sort
Range(Cells(1, 14), Cells(Lrow, 25)).Sort _
    Key1:=rKey1, Order1:=xlAscending, _
    Key2:=rKey2, Order2:=xlAscending, _
    Key3:=rKey3, Order3:=xlAscending, _
    Header:=xlYes, dataoption3:=xlSortTextAsNumbers, _
    MatchCase:=False, Orientation:=xlTopToBottom

'copy and paste header's format
Range(Cells(1, 1), Cells(1, 12)).Copy
Range(Cells(1, 14), Cells(1, 25)).PasteSpecial (-4122)

'reset Lrow because null rows in column A are not copied
Lrow = Cells(1048576, 14).End(xlUp).Row

'copy and paste formats
Range(Cells(2, 1), Cells(2, 12)).Copy

For i = 2 To Lrow Step 2
Range(Cells(i, 14), Cells(i, 25)).PasteSpecial (-4122)
Next

Range(Cells(3, 1), Cells(3, 12)).Copy
For i = 3 To Lrow Step 2
Range(Cells(i, 14), Cells(i, 25)).PasteSpecial (-4122)
Next

ActiveSheet.Range("$A:$A").AutoFilter
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
 
Last edited:
Upvote 0
Re: Macro to Copy, Sort, Paste Formats, Ignore Null

Thanks yky!

This almost works, however if there is a blank cell in column A or a formula in column A returns "" (like =if(A>1,"","") then an out of place row is inserted, in otherwise well sorted data.


Its hard to explain but a very simplified view of what I am seeing:
Original DataSorted Data
AA
BB
E
DD
E

<tbody>
</tbody>
Again any help is appreciated.
 
Upvote 0
Re: Macro to Copy, Sort, Paste Formats, Ignore Null

I had tested the code before posting and it had worked (for me, anyway). I inserted two blank rows to the data you posted, ran the code, and the pasted area was two rows less than the copied area. In my case, there was no formula. That might be the difference.

Would you post a sample excel workbook to any of the free file sharing site, preferably no password required to download?
 
Upvote 0
Re: Macro to Copy, Sort, Paste Formats, Ignore Null

I don't see empty cells in column A. There are cells that contain a hyphen in column A. Are those what you called null?
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Multiple IF with individual criteria for rate with results return value 'place' in header
Replies
7
Views
603
Greenbehindthecells
G
R
  • Question
Code to copy then paste certain columns as values
Replies
4
Views
317
Peter_SSs
Peter_SSs
G
  • Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
238
glaccounting
G
P
  • Solved
How to copy and paste the next row VBA Help.
Replies
8
Views
357
James006
James006
P
  • Question
Copying and transposing data from one worksheet to another
Replies
1
Views
353
jdellasala
J

Forum statistics

Threads
1,215,447
Messages
6,124,907
Members
449,195
Latest member
Stevenciu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top