CODEWITHSUNDEEP
vbaexcel
Martin
Martin

Reputation: 3

Nesting a macro within a macro

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.

Upvotes: 0

Views: 1606

Answers (1)

Pablo Santoro
Pablo Santoro

Reputation: 51

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!

Upvotes: 1

Related Questions