Code to Insert Column causes Macro to not function correctly...

**numbersbuff** · 01-27-2012, 03:31 PM

Trying to create a macro to delete rows based on contents of a column, complete a few sorts, add a column and then combine contents of 3 columns into 1 for each row respectively and then subtotal based on the new combined column.

Here is what I have that works but once I try to add column, macro does not complete the sorts at all or incorrectly depending on where the code for the insertion is placed and the code is edited based on placement of the code to insert the column.

Sub Delete()
    Range("A4:J992").Select
    Range("F4:F996").Replace 0, "", xlWhole
    Range("F4:F996").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
Sub Sort2()
    Range("A4:J996").Select
    ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Add Key:=Range("C4:C996"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Add Key:=Range("D4:D996"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Add Key:=Range("E4:E996"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort
    .SetRange Range("A4:K996")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
End Sub

Insertion Code...

Sub InsertColumn()
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Also can not get the concatenate or =D1&" "&E1&" "&F1 to fill the new C column...the formula works for the first cell and looks correct, meaning row number within formula changes, for the next cells but the outcome is the value of the first cell C1...

Help???

**shg** · 01-27-2012, 03:48 PM

Welcome to the forum, numbersbuff.

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Thanks

**numbersbuff** · 01-27-2012, 04:04 PM

Sorry first time on the forum and first time building Macro so if you could please explain CODE tags that would be helpful...

**Pepe Le Mokko** · 01-27-2012, 04:12 PM

Hi
Click on the forum rules button it's all explained there

**numbersbuff** · 01-27-2012, 04:21 PM

Thank you!

**jstephens** · 01-27-2012, 04:42 PM

I'm not 100% sure what you're asking, can you post an example of what you have pre-macro and then the desired results?

One thing to remember is that when deleting columns, you always want to go right to left since if you delete Column C, Column D will become Column C.

**numbersbuff** · 01-27-2012, 04:58 PM

Thanks for the tip on deleting columns, after I get the afore mentioned portions to work that is the next step.

Will post an example Monday but here is an over view:

Inventory Sheet has Columns A:J, and a variable number of rows up too 1000. Each row can have items in each column cell or only some.

First portion of Macro deletes any rows that lack data in F (quantity of item), the macro then sorts by first C (name of item), then D (size of item) and then E (color of item).

The macro works to here. I need to then insert a column at C and then combine info from now D,E and F in to C, then delete or hide D,E and F and create subtotals by the new C.

I will post a before and after on Monday...gotta run have a wonderful weekend!

**numbersbuff** · 02-07-2012, 05:05 PM

Originally Posted by jstephens

I'm not 100% sure what you're asking, can you post an example of what you have pre-macro and then the desired results?

One thing to remember is that when deleting columns, you always want to go right to left since if you delete Column C, Column D will become Column C.

I am sorry for the dely in this post. I did get the macro to work however I have to run each step separately for it to run correctly....maybe you can shed some light on the glitch??

Sub Step1()
    Range("A4:J992").Select
    Range("F4:F996").Replace 0, "", xlWhole
    Range("F4:F996").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
Sub Step2()
    ActiveWorkbook.Worksheets("47th Ave").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("47th Ave").Sort.SortFields.Add Key:=Range( _
        "C4:C992"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("47th Ave").Sort.SortFields.Add Key:=Range( _
        "D4:D992"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("47th Ave").Sort.SortFields.Add Key:=Range( _
        "E4:E992"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("47th Ave").Sort
        .SetRange Range("A4:J992")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "=RC[1]&"" ""&RC[2]&"" ""&RC[3]"
    Range("C4").Select
    Selection.AutoFill Destination:=Range("C4:C935"), Type:=xlFillDefault
    Range("C4:C935").Select
    Columns("C:C").EntireColumn.AutoFit
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("D:F").Select
    Selection.Delete Shift:=xlToLeft
    Range("A4:H925").Select
    Selection.Replace What:="LBS", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(4, 8), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
Sub Step3()
    For i = 3 To Cells(Rows.Count, 3).End(xlUp).Row
    If Cells(i, 3) Like "*Total*" Then
    Rows(i).EntireRow.Font.Bold = True
    End If
    Next i
End Sub