CODEWITHSUNDEEP
vbaexcel
Addy
Addy

Reputation: 11

VBA macros code

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

Upvotes: 0

Views: 192

Answers (2)

user4039065
user4039065

Reputation:

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.

Upvotes: 1

nwhaught
nwhaught

Reputation: 1592

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)

Upvotes: 1

Related Questions