1

****updated as sort now works, only need how to autofit rows, dynamically without defining rows to autofit. Columns withh always be same and can be hard code***

Newbie here, with limited VBA.

I have a current project, and although my question may seem basic, considering how many steps I have completed accurately, I cannot seem to autofit all rows, without defining the rows.

I can do it by a range, but every month the amount of entries on the report differs.

My macro essentials takes a report (name changes every month as it is exported by another application in a messy Excel format) and cleans it up. Later it will be transposed by other methodology into a SharePoint datasheet list.

I spell everything out in steps as I am more a workflow writer. If there is anything I can clean up to simplify, Id learn to love that as well.

Macro is as follows: (please forgive formatting errors from trying to copy into Question)

Sub ComponentBalRptCleanup()

'Unmerge all Cells in Worksheet
ActiveSheet.Cells.UnMerge

'Delete Columns A1 thru D1
Range("A1:D1").EntireColumn.Delete

'Delete Rows A1 thru A9
Range("A1:A9").EntireRow.Delete

'Cut and Paste Cells
Range("A2").Cut Range("A1")
Range("G1").Cut Range("F1")
Range("P1").Cut Range("O1")
Range("AA1").Cut Range("Z1")

'Sort by Column A to Remove Extra Rows from View
 Columns("A:AN").Sort key1:=Range("A:A"), order1:=xlAscending, Header:=xlYes

'Auto Fit Contents in Columns and Rows
     ActiveCell.Columns("A:AG").EntireColumn.Select
    ActiveCell.Columns("A:AG").AutoFit
ActiveCell.Rows("1:77").EntireRow.Select
       ActiveCell.Rows("1:77").EntireRow.AutoFit

'Delete Empty Columns
Range("B:B, D:D, G:I, K:L, N:N, P:Q, T:V, X:Y, AA:AB, AD:AF").EntireColumn.Delete

'Remove Wrap Text from Cell B1
Range("B1").WrapText = False

'Autofit Contents of Columns
Range("A1:AF1").Columns.AutoFit

'Autofit Row A2 Contents
Range("A2:A2").Rows.AutoFit

'Save File As
Application.GetSaveAsFilename

End Sub
user7263910
  • 9
  • 2
  • 6
  • 1
    [This will be helpful](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), as your entire macro is "hard-coded", it will be better to use explicit variables and to assign `Range` objects to each as needed, also to avoid `Activate` method... Apart from that: It's not clear from your code which parts need to be dynamic (something about the range size -- columns or rows? which line(s)? etc). – David Zemens Dec 07 '16 at 18:32
  • Also see [this answer](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) for ideas on how to find the *last* row or column in a range. – David Zemens Dec 07 '16 at 18:36
  • It is the rows that will vary per month, depending on entries (sales related). I need to be able to sort all rows (however many appear on that month's report), by column A. My code I wrote is very incorrect, and I cannot seem to get the expressions right in 'Sort by Column A to Remove Extra Rows from View. – user7263910 Dec 07 '16 at 18:39
  • The other issue is later in the Auto Fit Contents of Columns and Rows. My Columns is fine. It will never be more than AG per month. It however can exceed 77 rows. I do not know how to build the expression to autofit columns to column AG, and autofit all rows containing data, or just autofit entire sheet. – user7263910 Dec 07 '16 at 18:41
  • 1
    1) Are you suggesting that `Columns("A:AN").Sort...` doesn't actually *sort* your range? If not, please explain in more detail what is the problem with the sort. 2) For finding last row, please see second link provided above, attempt to implement, and if you get stuck, [edit your question](http://stackoverflow.com/posts/41024739/edit) with your updated code. – David Zemens Dec 07 '16 at 18:48
  • Is there a reason to have `Range.EntireRow.EntireRow`? I'm unfamiliar with this usage. – Rdster Dec 07 '16 at 18:56
  • I put that there to define it to sort the entire rows, not just column. I need entire rows, and their values, sorted by A. – user7263910 Dec 07 '16 at 19:13
  • But why twice?? – Rdster Dec 07 '16 at 19:22

1 Answers1

0

I would say that the main problem is with your sort. Sorting a variable range in VBA is tricky. I would strongly suggest using a named range. You can select the "Active" region of your spreadsheet and give it a name with the following statements:

Range("A1").Select
Selection.CurrentRegion.Select
Selection.Name = "myRange"

After that, use myRange instead of a static address. Here is a simple example:

Sub NameRangeAndSort()
' Create Named Range and Sort by 1st column
    Dim TotalRows As Integer
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Name = "myRange"

    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("A1") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("myRange")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

You also have EntireColumn repeated twice in a couple of your autofit statements. That should only be included once per statement.

tlemaster
  • 859
  • 5
  • 7
  • I remove the redudancies in both stating entire column twice, and entire row. Not sure what my reasoning was. It works perfectly without. I have got the sort to work, I had to fix an issue of my own error. It wasnt the code, it was error on my part for all columns I needed to define. Now I am down to 1 issue. Just how to autofit all rows, without stating autofit 1:77. Instead more dynamic, saying autofit all rows on sheet, or all rows with data. Either or. I will be updating my question shortly to account for only the one oustanding issue. I really appreciate all the questions, feedback, and e – user7263910 Dec 07 '16 at 19:44
  • David Zemens already posted a link that should show you how to do that. – Rdster Dec 07 '16 at 19:52