0

I would like integrate two macros code in to one. The first macros code is used to find the exact number of rows in a specific column. The second macros code is used to do a certain set of changes in the worksheet. I need the result of the first macros code to automatically be inserted in the second macros code. ( basically where the range is defined)

Macros code 1

Sub sbLastRowOfDColumn()
    'Find the last Row with data in a Column
    'In this example we are finding the last row of column D
    Dim lastRow As Long
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    End With
    MsgBox lastRow
End Sub

Macros code 2

Sub test()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "variable1"
    Columns("A:A").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "gdgs"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "6"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A360"), Type:=xlFillDefault

    'need the result of last row at the place of A360
    Range("A2:A360").Select
    ActiveWindow.SmallScroll Down:=-1034
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "dgdgsg"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "gdsgsdgsd"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "sdgsdgsfh"
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "dgsdgsgs"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "url"
    Range("G:G,H:H,I:I,J:J,K:K,L:L,M:M").Select
    Range("M1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.ClearContents
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "dgdfggsdgh"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "dgsdgdshshdh"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D360"), Type:=xlFillDefault

    'need the result of last row at the place of D360
    Range("D2:D360").Select
    ActiveWindow.SmallScroll Down:=-984
End Sub
MatthewD
  • 6,719
  • 5
  • 22
  • 41
Addy
  • 11
  • You can either change macro1 to a function for use in macro2 or just move code from macro1 to macro2 then use `lastRow` in place of D360. – findwindow Sep 29 '15 at 19:37
  • 1
    Just to be clear, use the `lastRow` in place of the **360** in D**360** like `"D2:D" & lastRow`. –  Sep 29 '15 at 19:56

2 Answers2

1

You need to change the first sub into a function. Functions return values, whereas subs just run procedures.

Function sbLastRowOfDColumn()
'Find the last Row with data in a Column
'In this example we are finding the last row of column D
Dim lastRow As Long
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
End With
 sbLastRowOfDColumn = lastrow 'this line sets the value that the function will return.
End Function

Then, anywhere in your second macro that you need this value, instead of the number, just put sbLastRowOfDColumn. For example:

Selection.AutoFill Destination:=Range("D2:D" & sbLastRowOfDColumn)

nwhaught
  • 1,562
  • 1
  • 15
  • 36
  • `Sub` can return values to objects passed `ByRef` :) – David Zemens Sep 29 '15 at 19:42
  • @DavidZemens - but they cannot be concatenated into a string construction. The variable can be altered with a call to the helper sub procedure and the variable would have to be part of the range address' string construction. –  Sep 29 '15 at 20:00
  • Correct @Jeeped I was just adding the comment for technicality's sake. – David Zemens Sep 29 '15 at 20:01
  • 2
    @DavidZemens -No worries. I'm just trying to maintain complete clarity given the entry level of the question. Too easy to cause confusion when a user is just starting out. –  Sep 29 '15 at 20:03
  • Actually that's a new one for me too. Thanks! – nwhaught Sep 29 '15 at 20:23
1

It really isn't clear on what you want to accomplish. You put a values into A1 then immediately .ClearContents on the entire column A. There are other examples of this put-a-value-erase-a-value coding. I get that you recorded it, but you need to step through it and remove the lines that do nothing. Possibly, reorganize the steps that you need to accomplish and re-record it.

Here is my attempt at cleaning up your code. I've commented a few spots that look like trouble.

Sub test()
    Dim lr As Long
    With ActiveSheet
        lr = .Cells(Rows.Count, "D").End(xlUp).Row

        .Columns("A:A").ClearContents
        .Range("A1") = "gdgs"
        .Range("A2").FormulaR1C1 = "6"
        .Range("A2:A" & lr).FillDown    '<~~see how lr is used

        .Range("B1") = "dgdgsg"
        .Range("C1") = "gdsgsdgsd"
        .Range("D1") = "sdgsdgsfh"    '<~~this gets cleared further down

        .Columns("E:E").Delete Shift:=xlToLeft
        .Range("E1") = "dgsdgsgs"
        .Range("F1") = "url"

        'i'm not sure if these columns are correct as you deleted column E:E above
        'and shifted everything left one column
        .Range("G:G,H:H,I:I,J:J,K:K,L:L,M:M").Delete Shift:=xlToLeft

        .Columns("D:D").ClearContents
        .Range("D1") = "dgdfggsdgh"
        .Range("D2").FormulaR1C1 = "dgsdgdshshdh"   '<~~maybe use .Range("D2").Formula for regulat xlA1 style formulas
        .Range("D2:D" & lr).FillDown    '<~~see how lr is used
    End With
End Sub

As you can see, there is no reason to use the .Select process that the macro recorder uses. The code that the macro recorder supplies is very verbose and can almost always be trimmed down to what is actually necessary.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Community
  • 1
  • 1