Nesting a macro within a macro

Question

I have two macros which independently work. I want to nest one within the other.

I receive a recurring file that has a couple of rows of sporadic footers on the bottom of them. I need to remove these footers. The number of rows in each file varies, but there is always an empty row between the end of the data and the footer.

The first macro finds the empty row by looking in column A

Sub FTPstep2()
'
' FTPstep2 Macro
'

'
If Application.WorksheetFunction.CountA("A:A") = 0 Then
    [A1].Select
Else
    On Error Resume Next
    Columns(1).SpecialCells(xlCellTypeBlanks)(1, 1).Select
    If Err <> 0 Then
        On Error GoTo 0
        [A65536].End(xlUp)(2, 1).Select
    End If
    On Error GoTo 0
End If
End Sub

The second macro deletes everything below row "X"

Sub FTPstep3()
'
' FTPstep3 Macro
'
With Sheets("Sheet1")
    .Rows( X & ":" & .Rows.Count).Delete
End With
End Sub

I'd like to nest the first macro (FTPstep2) where the "X" is in the second macro (FTPstep3). I've tried a variety of routes but it tends to not like the ampersand or expects end statements, etc.

Are you saying that e.g you have data in A1:A500 and then something in A1000:A1005 and you want to delete the latter? — SJR, Jan 23 '17 at 21:44
It's also best to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — BruceWayne, Jan 23 '17 at 21:59

Pablo Santoro · Accepted Answer · 2017-01-23T23:34:31.507

What you need is a function that returns a value you can use in the macro "FTPstep3"

Check this code to see if it works

Function FTPstep2() As String
'
' FTPstep2 Macro
Dim returnValue As Integer

'
If Application.WorksheetFunction.CountA("A:A") = 0 Then
    returnValue = 1
Else
    On Error Resume Next
    returnValue = Columns(1).SpecialCells(xlCellTypeBlanks)(1, 1).Row
    If Err <> 0 Then
        On Error GoTo 0
        returnValue = [A65536].End(xlUp)(2, 1).Row
    End If
    On Error GoTo 0
End If

FTPstep2 = returnValue

End Function

Sub FTPstep3()
'
' FTPstep3 Macro
'
With Sheets("Sheet1")
    .Rows(FTPstep2 & ":" & .Rows.Count).Delete
End With

End Sub

This code will delete any rows below the first blank cell it finds in column "A".

Functions are similar to subs, they may be given any input (in this case I don't pass any parameter into function "FTPstep2") and they return a value that can be used in other procedure (notice the line FTPstep2 = returnValue). We also eliminate all Select instructions, as they are unnecessary here, and it is not recommended to use them, as VBA doesn't need to select cells in order to modify them. Even in this case, we rely on moving throughout the worksheet using End()and Offset(), you should see if this can be improved

By the way, I would choose more informative names for your subs and functions, It'll make you easier to reuse the functions you write and maintain them. For example, you could make yourself an Add-in and reuse your code as you see fit, or export a code module which has all kinds of useful custom functions you may want to reuse in other projects.

Happy coding!

Edit: Corrected short definition of VBA Function, thanks @barrowc for pointing this out!

"Functions are similar to subs but they don't modify any worksheet" sounds like a description of a UDF (i.e. a particular restricted kind of `Function` which can be called in a formula) rather than a description of a general VBA `Function` — barrowc, Jan 23 '17 at 23:08
I wanted to give a simple definition of what a `Function` is, kind of "like a sub but it gives a value as an output". Maybe the wording I used isn't the most accurate. I could use another definition and edit the answer, what would you suggest? — Pablo Santoro, Jan 23 '17 at 23:23
The answer looks fine except for the "but they don't modify any worksheet" part so maybe just remove that bit — barrowc, Jan 23 '17 at 23:30
I agree, now it looks better, thanks for your help @barrowc! — Pablo Santoro, Jan 23 '17 at 23:35
Thanks Pablo! This worked wonderfully! And yes I will give them more informative names. — Martin, Jan 24 '17 at 14:42

Nesting a macro within a macro

1 Answers1