2

I'm looking for a more efficient, less hard-coded way of transposing an array and then autofilling formulas in adjacent columns. Here is my current code for transposing my array in a specific spot on the sheet and autofilling the columns:

 If Len(Join(myArray)) > 0 Then
    ActiveWorkbook.Sheets("Delta Summary").Range("A3:A" & UBound(myArray) + 2) = WorksheetFunction.Transpose(myArray)
    ActiveWorkbook.Sheets("Delta Summary").Range("B3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFill Destination:=Range("B3:K17"), Type:=xlFillDefault
Else: End If

The goal is to transpose the array starting in cell A3 on sheet "Delta Summary". My code accomplishes this, but I'm wondering if there's a better way to do it. For reference, I loop through this array and transpose it several times based on different criteria. I transpose the array beginning at cells A3, A20, A37,..., and A224. Each section has 15 cells allocated for data.

As for the auto-fill, I'd like to auto-fill the formulas in columns B:K down to the last populated cell in column A for that pre-defined range (ex. A3:A17, A20:34, etc.). I don't know how to find the last populated cell for a pre-defined range, so I have this hardcoded.

I'm still learning, so any insight would be greatly appreciated!

Edit: Here is one example of the looping criteria I use to populate my array:

ReDim myArray(0)
For i = 1 To LastCurrID
    If ActiveWorkbook.Sheets("Weekly Comparison").Range("N" & i) = "N" And ActiveWorkbook.Sheets("Weekly Comparison").Range("J" & i) = "Billing" Then
    myArray(UBound(myArray)) = ActiveWorkbook.Sheets("Weekly Comparison").Range("A" & i)
    ReDim Preserve myArray(UBound(myArray) + 1)
End If
Next i

Edit #2: For those who are curious, here's the completed code. I only slightly changed what was commented below.

    ReDim myArray(0)
For i = 1 To LastCurrID
    If wkb.Sheets("Weekly Comparison").Range("N" & i) = "N" And wkb.Sheets("Weekly Comparison").Range("J" & i) = "Billing" Then
        myArray(UBound(myArray)) = wkb.Sheets("Weekly Comparison").Range("A" & i)
        ReDim Preserve myArray(UBound(myArray) + 1)
    End If
Next i

For y = LBound(myArray) To UBound(myArray)
    If Len(Join(myArray)) > 0 Then
        With wks
            .Range("A" & x & ":A" & UBound(myArray) + x - 1) = WorksheetFunction.Transpose(myArray)
            Dim lRow As Long
            lRow = .Range("A" & x).End(xlDown).Row - x + 1
            .Range("B" & x).Resize(1, 10).AutoFill _
                Destination:=.Range("B" & x).Resize(lRow, 10), Type:=xlFillDefault
        End With
    End If
Next
x = x + 17
Community
  • 1
  • 1
kschindl
  • 223
  • 2
  • 12
  • 1
    To find the last cell look [here](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba). – Scott Craner Oct 23 '15 at 17:52
  • If I'm not mistaken, the method you linked assumes data beginning at cell 1 in the column without any headers to break it up. In the given example, LastRow = .Range("E" & .Rows.Count).End(xlUp).Row will only find the last cell in ALL of column E, not a specific range. – kschindl Oct 23 '15 at 17:59
  • You Said `I'd like to auto-fill the formulas in columns B:K down to the last populated cell in column A` so you use the method on the link to find the last row in column A then fill to `Range("B3:K" & lastrow)` – Scott Craner Oct 23 '15 at 18:02
  • `for reference, I loop through this array ...` can you include the looping part of the code as well? and the part that defines the criteria? I think I can help you with you what you are asking, but having the loop and criteria part will help me determine the variables to make it work. – Scott Holtzman Oct 23 '15 at 18:10
  • 1
    @ScottCraner You left the most important part of the quote out where I said "last populated cell in column A for that pre-defined range (ex. A3:A17)." So, I'd like to find the last populated cell between cells A3:A17. – kschindl Oct 23 '15 at 18:24
  • There were many different methods of finding the last cell. you would use `Lastrow = Range("A3").end(xldown).row`. There are many ways to do this. I like @ScottHoltzman method. I believe that there were at least three different method mentioned in the link. – Scott Craner Oct 23 '15 at 18:35

1 Answers1

0

EDIT (Based on OP Update Question with Looping)

From the way you build your array, it seems like the array is loading with the last row of the data range to be copied (within the 15 row limit) for each range.

The below will loop through the array again, and will set a factor of 17 to x for each loop (starting at 3) and will find the last row within the specified range starting at 'Bx' and uses the .Resize method to do the AutoFill:

'always best to qualify the workbook, worksheet objects with a variable
Dim wkb As Workbook, wks As Worksheet
Set wkb = Workbooks("myWKb")
Set wks = wkb.Sheets("Delta Summary")

Dim x As Long, y As Long
x = 3

For y = LBound(myArray) To UBound(myArray)

    If Len(Join(myArray)) > 0 Then

        With wks

            .Range("A" & x & ":A" & UBound(myArray) + 2) = WorksheetFunction.Transpose(myArray)

            Dim lRow As Long
            lRow = .Range("A" & x).End(xlDown).Row

            .Range("B" & x).Resize(1, 10).AutoFill _
                Destination:=.Range("B" & x).Resize(lRow, 10), Type:=xlFillDefault

        End With

    End If

    x = x + 17

Next
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Sorry if I created confusion, I couldn't get the code to paste nicely in the comments so I edited my original question with the looping criteria. – kschindl Oct 23 '15 at 18:35
  • Hey Scott, thanks a lot for your solution. It works with one caveat: it seems to always autofill two rows past the last populated cell in column A. I manipulated my data (2 entries, 5, entries, etc.), and it always gave me two extra rows of #N/A from my vlookups. Do you know why this would happen? – kschindl Oct 26 '15 at 15:38
  • Also, I'm not too familiar with the Resize property. If I understand it correctly, it's taking the data from B3:K3 (10 columns) and resizing the # of rows to equal the row returned by `lRow`. Is my understanding on point? – kschindl Oct 26 '15 at 15:41
  • your understanding is spot on @kschindl. I also just update the first line under `With wks`. I didn't put the variable in there before, now I did, so make sure to use the updated code. – Scott Holtzman Oct 26 '15 at 15:46
  • for the two extra cells, can you remove the + 2 in the `UBound(myArray) + 2)` statement, or do you need that in there? If you do need it, then change the `lRow` line to `lRow = .Range("A" & x).End(xlDown).Row - 2` – Scott Holtzman Oct 26 '15 at 15:49
  • Without the `+ 2`, 2 entries in the array don't get transposed. Honestly, I'm not sure why I need the +2, but I think it has something to do with the fact that `UBound` on an empty array returns -1 (I think). Regardless, with the `- 2` in the `lRow`, it works great! Now my final request would be to keep the formatting from the original cell that populated the array. Can that be done? – kschindl Oct 26 '15 at 16:02
  • It's possible, but it may be a bit tricky, since you are loading the values to an array and spitting them back out. If you formatting is all different, it may be very tricky. Either way, it's a whole separate question and *outside* the scope of this question. Please give it a try on your own and if you get stuck, post another question with details. Also, please mark this one as solved. – Scott Holtzman Oct 26 '15 at 16:14