multiple functions in a macro

[ngkf7]

Hi, I am using VBA to write a Macro and would like to put another function within the current Sub __ code. Is it as simple as java/c++/etc where I just make a new Sub and call it in the rest of the general sheet?

Also, I want this function to find the last row of data on a specific sheet and pase a new selection right underneath it. I think I have this part down though, but I'm not sure, I know there has to be a more efficient way than what I've done.

Thank you, any help on this would be greatly appreciated.

 

[Lewiy]

Yes, you can just write another Sub in the same Module. If you record 2 macros using the macro recorder and then look at the module, you will see that it puts them one after the other in the same place.

To find the last used row in a column, use:

Cells(Rows.Count,1).End(xlUp).Row

Where “1” is the column you are checking (1=A, 2=B, etc.)

Hope that helps!

 

[ngkf7]

Thanks

Thanks a lot!

 

[ngkf7]

oops

I'm still an amateur at this...do I put it as:

Function xx ()
...
End Function

Or do I put it as a new Sub x...End Sub?

If it's the first one, do I put it outside or within the Sub?

Thanks

 

[Lewiy]

You would structure it like this:

Sub FirstSub()
'code here
End Sub

Sub SecondSub()
'more code here
End Sub

Function FirstFunction()
'even more code here
End Function

Each Sub separate from the rest. If you are writing a User Defined Function then you would use Function instead of Sub. Hope that makes sense!

 

[Darren Bartrup]

Hi ngkf7,

I normally put it in a function:

Function LastRow(wrkSht As Worksheet, lCol As Long) As Long
    LastRow = wrkSht.Cells(65536, lCol).End(xlUp).Row
End Function

This can be placed above or below your Sub, or in a seperate module.

This can then be called from within your procedure using something like:

lLastQuestion = LastRow(ThisWorkbook.Worksheets("Raw Data"), 1)

Which will find the last row in column 1 of the 'Raw Data' worksheet.
Or if you're worried about the sheet name changing you could use:

lLastQuestion = LastRow(Sheet1,1)

Sheet1 is the CodeName for the 'Raw Data' sheet (i.e. in the Project Explorer on your visual basic editor it's the name of the sheet that's not in brackets and as far as I know can only be changed from the VBE).