multiple functions in a macro

ngkf7

New Member
Joined
Jul 23, 2007
Messages
11
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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:
Code:
Cells(Rows.Count,1).End(xlUp).Row
Where “1” is the column you are checking (1=A, 2=B, etc.)

Hope that helps!
 

ngkf7

New Member
Joined
Jul 23, 2007
Messages
11
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
You would structure it like this:
Code:
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

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
Hi ngkf7,

I normally put it in a function:
Code:
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:
Code:
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:
Code:
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).
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,888
Messages
5,766,948
Members
425,389
Latest member
Naresha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top