0
I know there any many similar questions to this, but none seem to match exactly... I have a basic Stock List, and want to help to speed up stocktaking, so am looking to create a macro to do the below:
Select a range and sort column E, F and C, then where Column E has a value change, insert a blank coloured line.
===========================================================================================
So im very new to VBA, but have managed to create a 3 part macro:
Macro1 - Sorts the data Macro2 - Inserts a blank line at value change Macro3 - Assigned to a button, this runs Macro1 and Macro2
All I need to do now, is change the blank line coloured???
Sub sort()
Rows("5:5").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Worksheets("HEAT SEAL").sort.SortFields.Clear
ActiveWorkbook.Worksheets("HEAT SEAL").sort.SortFields.Add Key:=Range( _
"E5:E407"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("HEAT SEAL").sort.SortFields.Add Key:=Range( _
"A5:A407"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("HEAT SEAL").sort.SortFields.Add Key:=Range( _
"F5:F407"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("HEAT SEAL").sort
.SetRange Range("A5:J407")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Sub Split()
rw = 5
myval = ActiveSheet.Cells(rw, 5).Value
While myval <> ""
While myval = ActiveSheet.Cells(rw, 5).Value
rw = rw + 1
Wend
ActiveSheet.Rows(rw).EntireRow.Insert
rw = rw + 1
myval = ActiveSheet.Cells(rw, 5).Value
Wend
End Sub
Sub run()
Application.run "STOCK1.xls!sort"
Application.run "STOCK1.xls!Split"
End Sub
1I'm sure they do exist. Any way, what have you tried so far, or do you want some one to just do your work for you? It may be easier if you share the code you've tried so we can see where it has gone wrong. – Dave – 2013-12-16T08:29:53.657
Im still very new to VBA, but here are a few macros I have tried: – user3106405 – 2013-12-16T08:34:56.247