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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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!
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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
Back
Top