what are macros?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please can somebody tell me what macros are? and of what use can I make of
them? thanks, I use excel 2003
 
Was this question ever answered? I would really like to know the answer
myself. I looked on the other posting sites, ans I did not see the question
anywhere else.
 
In it's simplest form, imagine yourself performing a number of repetitive
actions in Excel day after day, eg setting up print headers, formatting,
titles blah blah blah. All of this can be recorded and then at the push of
a button, the recorded actions will happen automatically with no further
intervention on your behalf. This is a macro.

They can get far more complicated than that though, and you can write code
that will pretty much do anything that you find to be laborious, and again
it can all be run at the touch of a button.

As an example:-

Sub Delete_Rows()

Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If InStr(UCase(Cells(RowNdx, "A").Value), "OLD") Then
Rows(RowNdx).Delete
End If
Next RowNdx

End Sub

What this macro will do is to run through all the cells in Col A of whatever
sheet you are on, starting from the last filled cell in Col A, look for the
text "OLD" in the cell and if it finds it it will delete the entire row.
This would be a rather laborious job on say thousands of rows of data.

So how do I use code like that then?

The following is a step by step guide for using most of the bits of code you
will see posted in the groups:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane
you need to search for your workbook's name, and when you find it you may
need to click on the + to expand it. Within that you should see the
following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook

If you have named your sheets then those names will appear in the brackets
above as opposed to what you see at the moment in my note.

Right click on the where it says VBAProject(Your_Filename) and choose
'Insert Module' and it will now look like this

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1

Double click the Module1 bit and then paste in the following code starting
at the Sub Delete_Rows() bit and finishing at the End Sub bit.


Sub Delete_Rows()

Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If InStr(UCase(Cells(RowNdx, "A").Value), "OLD") Then
Rows(RowNdx).Delete
End If
Next RowNdx

End Sub

Then hit File / Close and return to Microsoft Excel and save the file. Now
just do Tools / Macro / Macros / Delete_Rows


If you then want to get rid of the macro, then do the following:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane
you need to search for your workbook's name, and when you find it you may
need to click on the + to expand it. Within that you should see the
following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
etc..........................
ThisWorkbook
Modules
Module1

Right click on the Module1 and select remove. When prompted with a question
re exporting, just hit no. Then hit File / Close and return to Microsoft
Excel and save the file.
 
two answers that I didn't see in the other one that I just thought I saw.

LOL - Had me reading that one a couple of times there Dave :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

David McRitchie said:
Sorry I thought I had seen the same question answered today, since I can't find
it I was mistaken. Anyway, looks like Ken Wright covered the topic
of what is a macro is and much better than the two answers that I didn't see
in the other one that I just thought I saw. Some of my favorite macros are in
http://www.mvps.org/dmcritchie/excel/proper.htm
http://www.mvps.org/dmcritchie/excel/join.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


Kulakula said:
Was this question ever answered? I would really like to know the answer
myself. I looked on the other posting sites, ans I did not see the question
anywhere else.
Click to expand...
Click to expand...
 
(applause)
Appreciation from the lurkers party.

| In it's simplest form, imagine yourself performing a number of repetitive
| actions in Excel day after day, eg setting up print headers, formatting,
| titles blah blah blah. All of this can be recorded and then at the push of
| a button, the recorded actions will happen automatically with no further
| intervention on your behalf. This is a macro.
 
Back
Top