How To Select Odd Numbers In Excel?

Excel/VB Macro Assistance

• I created the macro below in Excel (line numbers added for reference): [START MACRO] 1 Sub Macro5() 2 Rows("3:3").Select 3 Selection.Insert Shift:=xlDown 4 Range("A2:G2").Select 5 Selection.Copy 6 Range("A3").Select 7 ActiveSheet.Paste 8 Range("$J$1").Select 9 Application.CutCopyMode = False 10 Selection.Copy 11 Range("H3").Select 12 ActiveSheet.Paste 13 Range("J2").Select 14 Application.CutCopyMode = False 15 Selection.Copy 16 Range("I3").Select 17 ActiveSheet.Paste 18 End Sub [END MACRO] I need help adding code that will increment some of the cells and repeat the code. For example, the next thing I want it to do is go back to line 2 and repeat the same steps, but this time starting on the next row down (row 4). Also on this second time through, the range values in lines 4 and 6 also need to have the rows increment by +1 (so line 4 becomes "Range("A3:G3").Select.") Basically, I want to start this macro from the active cell and have it repeat until it hits a blank row and then stop. Please provide actual code that I can cut and paste into my macro to make it work. Thanks! --H

• Answer:

  headless, Based on what I see in your sample spreadsheet, try this macro. It should do your whole sheet in one pass, rather than having to do a pass for each row. The code is commented. Please ask for clarification if you need something explained further. Paste the macro into the code module for the sheet you want to affect. Sub PrepForPivot() Dim intCurrentRow As Integer Dim intNextRow As Integer Dim intOuterColumn As Integer Dim intInnerColumn As Integer ' Start at the first row below the headers intCurrentRow = 2 ' Run until we hit a blank row Do Until Cells(intCurrentRow, 1).Value = "" ' Initialize the counter that tells us ' how many rows we added intNextRow = 0 ' Check each of the 3 driver columns to ' see if we should make a row for it For intOuterColumn = 10 To 12 ' If the cell is marked for that row, make a pivot row If Cells(intCurrentRow, intOuterColumn).Value <> "" Then ' Insert a blank row Me.Rows(intCurrentRow + intNextRow + 1).Insert ' Copy the values from the first 7 columns For intInnerColumn = 1 To 7 Cells(intCurrentRow + intNextRow + 1, intInnerColumn).Value = Cells(intCurrentRow, intInnerColumn).Value Next intInnerColumn ' Enter the correct header text in column 8 Cells(intCurrentRow + intNextRow + 1, 8).Value = Cells(1, intOuterColumn).Value ' Increment the rows added counter intNextRow = intNextRow + 1 End If Next intOuterColumn ' Move down to the next original row, skipping ' the rows we added in code to avoid an endless ' loop and lots of incorrectly repeated data intCurrentRow = intCurrentRow + intNextRow + 1 Loop End Sub Note: No line of code should wrap. Additional Resources: Read the Excel Help for the Cells collection. It shows several examples of looping through cells in your spreadsheet. Search Strategy: None Good luck with your Excel project! - Hammer