Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I have exported data from an application that only displays a category header once for all items in a category. I'd like to use pivot table to summarize the data, but since the category field isn't displayed next to each item I will have to copy the category name for each of the items in each category. I recorded the macro below, but I can't figure out how not to overwrite the next category title. I've got about 300 category names to copy down through the remaining 2700 or so rows, so I will certainly appreciate your help.

    Sub COPYDOWN()
    '
    Selection.Copy
    ActiveCell.Select
    Range(Selection, Selection.End(xlDown)).Select

    ' At this point I would like to insert xlUp (or something like it) so that the Paste doesn't overwrite the next item below

    ActiveSheet.Paste
    Selection.End(xlDown).Select
    'And here to move down to the next item.
    End Sub
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does this do what you want?

    It fills in the blank cells from B7 to the row in B that matches the last row that has something in column C, with the contents of the cell above it:

    Code:
    Sub FillCategories()
      Range(Range("B7"), _
    	Cells(Cells.Rows.Count, 3).End(xlUp).Offset(0, -1)). _
      	SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=+R[-1]C"
      Columns("B:B").Copy
      Columns("B:B").PasteSpecial Paste:=xlValues
      Application.CutCopyMode = False
    End Sub

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    WOW! Thanks Steve. This is amazing. I was really just expecting something to help with one category at a time and then I would reposition the cursor restart a macro manually. The code even correctly passes over the extra category headings (which I think are exported incorrectly from the app as new page column headers).

    I had to modify the code a little to run on my version of the report. The sample I attached started at B7; the entire report actually started at F8.
    Range(Range("f8"), _
    Cells(Cells.Rows.Count, 2)
    ' and the formulas to "F:F"

    But I tested the 3k plus rows and it works fine.

    I'd like to know more about these lines of code in case I need to modify again for the next month's report I'm about to prepare:
    Cells(Cells.Rows.Count, 3).End(xlUp).Offset(0, -1)). _
    SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=+R[-1]C"

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The complete line is:

    Range(Range("B7"), _
    Cells(Cells.Rows.Count, 3).End(xlUp).Offset(0, -1)). _
    SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=+R[-1]C"

    The code takes the blank cells in the range in Col B and puts in the value of the cell right above it.

    Broken out:
    Cells.Rows.Count gives the number of rows in the sheet (in older code for XL97-2003, 65536 was often explicitly used. But since XL2007 has more rows now, this keeps it compatible with both versions).

    Cells(Cells.Rows.Count, 3) is thus the last row in column C (the 3rd col)

    Cells(Cells.Rows.Count, 3).End(xlUp) goes from the last row in Col C goes upward stopping at the first non-blank cell in C (this is in your example C320, the last non-blank cell in the range).

    Cells(Cells.Rows.Count, 3).End(xlUp).Offset(0,-1) goes from the last non-blank cell in Col C and goes 0 rows down and 1 column to the left (positive numbers would go to the right) to be at (in your example B320, the last row of interest in column B. This is equivalent to selecting C65536 and hitting [End][Up arrow] and then hitting the [Left Arrow]. It uses col c instead of B since B does not go all the down as needed, D could also be used...

    Range(Range("B7"), _
    Cells(Cells.Rows.Count, 3).End(xlUp).Offset(0, -1))
    thus defines the range of interest in starting in B7 and ending at the last desired row in column B (B7:B320 in your example)

    Range(Range("B7"), _
    Cells(Cells.Rows.Count, 3).End(xlUp).Offset(0, -1)). _
    SpecialCells(xlCellTypeBlanks)
    this selects the blank cells in the range in Col B. (it is equivalent to selecting B7:B320 and edit - go to... [Special...] - Blanks - [OK])


    Range(Range("B7"), _
    Cells(Cells.Rows.Count, 3).End(xlUp).Offset(0, -1)). _
    SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=+R[-1]C"
    Thus it takes the blank cells in the range in col B and puts in the formula of the cell 1 row above (R[-1]) and in the same column (C).

    The rest of the code just copies and pastes the values to remove the formulas and then gets rid of the "copy highlight"

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Steve. You've written the explanation so well even I can follow it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •