using variable to define range to select

Question

I need to use a variable (CatCount) to define how wide a range of cells I grab to copy.  My expression will look somethin like

activecell.offset(-1,15).range(??).copy

and then i'll transpose that range vertically to another spot.  Cannot figure out how to get something like 'Range("A1:?1"), where the ? is a variable each time I run the loop.

Thanks for any help.

Answer 1

If I understand what you are attempting to do, then I have a much simpler macro for you to use. Let's see if I understand what you have and what you want to do with it. You have a count value in Column A and Columns B and C contain some information that is to be repeated for each data item in the rest of the row. Columns D through W have data in them (by the way, you posted Columns N through W in your description above, but I assumed that was wrong), possibly with blanks or runs of blanks located between them. You want to move all the non-blank data into Column D, one piece of data per row. To do that, you want to insert the necessary amount of rows to accommodate the data, then you want to fill the blanks in Columns A through C with the data in the non-blank cells above them. Does that sound right? Assuming your answer was yes, I think this much shorter macro will do that...

Sub TransposeData()

  Dim X As Long, LastRow As Long, LastColumn As Long, DataCount As Long, NonTransposableColumnCount As Long, Area As Range

  Const StartRow As Long = 2

  Const FirstTransposableColumn As String = "D"

  NonTransposableColumnCount = Columns(FirstTransposableColumn).Column - 1

  LastRow = Cells(Rows.Count, "A").End(xlUp).Row

  For X = LastRow To StartRow Step -1

    Cells(X, FirstTransposableColumn).Resize(1, 20).SpecialCells(xlCellTypeBlanks).Delete xlShiftToLeft

    LastColumn = Cells(X, Columns.Count).End(xlToLeft).Column

    DataCount = LastColumn - NonTransposableColumnCount

    Rows(X + 1).Resize(DataCount - 1).Insert

    Cells(X, FirstTransposableColumn).Resize(DataCount) = WorksheetFunction.Transpose(Cells(X, FirstTransposableColumn).Resize(1, 20))

    Cells(X + 1, "A").Resize(DataCount - 1, NonTransposableColumnCount).Value = Cells(X, "A").Resize(1, NonTransposableColumnCount).Value

    Cells(X, FirstTransposableColumn).Offset(0, 1).Resize(1, DataCount - 1).Clear

  Next

End Sub

Note that above code does not use the count value in Column A at all. So, if it would no screw up the rest of your worksheets (because of referencing into the sheet this macro is run against), you could remove Column A completely (that is, select the column and Edit/Delete it) and use this code instead (it is the above code modified to repeat what would be in Columns A and B after the count value column has been deleted...

Sub TransposeData()

  Dim X As Long, LastRow As Long, LastColumn As Long, DataCount As Long, NonTransposableColumnCount As Long, Area As Range

  Const StartRow As Long = 2

  Const FirstTransposableColumn As String = "C"

  NonTransposableColumnCount = Columns(FirstTransposableColumn).Column - 1

  LastRow = Cells(Rows.Count, "A").End(xlUp).Row

  For X = LastRow To StartRow Step -1

    Cells(X, FirstTransposableColumn).Resize(1, 20).SpecialCells(xlCellTypeBlanks).Delete xlShiftToLeft

    LastColumn = Cells(X, Columns.Count).End(xlToLeft).Column

    DataCount = LastColumn - NonTransposableColumnCount

    Rows(X + 1).Resize(DataCount - 1).Insert

    Cells(X, FirstTransposableColumn).Resize(DataCount) = WorksheetFunction.Transpose(Cells(X, FirstTransposableColumn).Resize(1, 20))

    Cells(X + 1, "A").Resize(DataCount - 1, NonTransposableColumnCount).Value = Cells(X, "A").Resize(1, NonTransposableColumnCount).Value

    Cells(X, FirstTransposableColumn).Offset(0, 1).Resize(1, DataCount - 1).Clear

  Next

End Sub

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 2

I love all that is learnable on this.  Thanks. 

works like a charm so far.

Answer 3

Thanks, Rick.  Sorry I could have been clearer.

I have a loop i'm running on each of many rows.  In each row, the first cell indicates how many horizontal cells (our of a range of 20, let's say) are nonblank.  for each row, i'm inserting the number of rows that equals those nonblanks cells less 1 (because the first row is staying, I only need X-1 rows additionally), and then i need to transpose those horizontal cells vertically into the new rows (so they are all in the same column).  My challenge now is that I can get to the insertion of the right number of rows, but then I want to copy the block of cells that are horizontally laid out in one shot, using the counter I have for each row (the counter that tells me how many of these horizontal cells are filled).  I don't know how to incorporate that into my macro.  here's what i have

Sub HorizontalCategoriesToVertical()

'

' HorizontalCategoriesToVertical Macro

' using counter cell in column A, inserts lines to convert horizontally listed categories to vertical (for use in pivot)

'

'

Dim RowCounter As Long

Dim EndCell As Long

Dim CatCount As Long

EndCell = Range("A:A").Find(What:="EndCell", LookAt:=xlWhole).Row

For RowCounter = ActiveCell.Row To EndCell Step 1

    CatCount = ActiveCell.Value

    If CatCount > 1 Then

        Range("A" & (RowCounter + 1) & ":A" & (RowCounter + CatCount - 1)).EntireRow.Insert

        ActiveCell.Range("A1:C1").Copy

        ActiveCell.Offset(1, 0).Range("A1" & ":A" & CatCount - 1).Select

        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

            False

        Application.CutCopyMode = False

 '       ActiveCell.Offset(-1, 15).Range().Select

        ActiveCell.Offset(rowOffset:=-1, columnOffset:=CatCount - 1).Activate

    End If

Next RowCounter

End Sub

endcell is a cell i use at the end of my long list of rows, to let the loop know it's done.

catcount is the column A cell of each row, which formulaically tells me how many nonblank cells there are in columns N-W (there are no more than 20 possible, so W is safe)

based on some other online advice, the insert line conveniently (and without moving the activecell) inserts the right number of lines below where the activecell is

I'm then copying the first 3 columns of info (I need this repeated on each line) down.

then the last two lines before the endif are two versions of attempts at selecting a range that start from (-1,15) offset of where I am, and copying that range for transposing. 

I coudl use the following help:

1. rather than having to rely on the calculation of "counta(N2:W2)" on each line (the row number obviously changing), I'd love to assign a variable in my loop that simply tells me how many nonblanks there are for the given row.  I can create an additional variable if you can give me the code to do that count
2. rather than copy/pasting down the first three cells, I'd love to replicate (a) select cells A-C of current row, (b) ctrl-shift-down jump to the next nonblank cell, (c) move the selection up one row (so as to not overwrite the next set of data), (c) fill down.  If you can help there, would be great
3. the main problem, which is grabbing a range that is defined by a number of nonblank cells (my variable, whether defined as my current in-cell formula or something the macro calculates each time it runs the process on a new row), and telling my macro to go off a few cells from where it sits, and copying a number of cells from left to right.  I know that a number of vertical rows can be done with "Range("A"&1&":A"&rownum)".  I don't know how to get a number of columns, in place of the "A's".

thanks for any and all help.

Answer 4

Cannot figure out how to get something like 'Range("A1:?1"), where the ? is a variable each time I run the loop.

You are looping letters? If so, try concatenating the text you know with the variable's name...

Range("A1:" & CatCount & "1")

I need to use a variable (CatCount) to define how wide a range of cells I grab to copy.  My expression will look somethin like

activecell.offset(-1,15).range(??).copy

and then i'll transpose that range vertically to another spot.

Maybe you need to give us some more information about what you are doing here.

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.