PDA

View Full Version : macro to remove headers in a multiple sheet spreadsheet



fbastian
08-31-2008, 10:11 PM
Hi,

does anyone have an excel macro which can remove headers (first rows) in a multiple sheet spreadsheet?

I'd appreciate it very much if you could pass it on. Even if it is a macro that does something similar.

thanks for your assistance.

cheers :beerchug:

shamsam1
08-31-2008, 10:43 PM
u can try some thing like this

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Range("A1").Select
Selection.EntireRow.Delete

fbastian
08-31-2008, 10:57 PM
Hi, thanks for the help but, i get the msg below when i run it.


"invalid outside procedure".

shamsam1
08-31-2008, 11:28 PM
do check with the sheets names...
else
create a new module

Sub del()
Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
'Cells.Select
Selection.ClearContents
End Sub

mdmackillop
09-01-2008, 12:15 AM
Option Explicit
Sub ClearHeaders()
Dim sh As Worksheet
For Each sh In Sheets
sh.Rows(1).ClearContents
Next
End Sub

fbastian
09-01-2008, 12:17 AM
ok, we are making progress! :yes
but on the line of code in bold, how can i include the other sheets?
it did work on the "Unreleased" one but the others still have the header.
it must be something very simple...

Sub del()
Sheets(Array("Unreleased", "Pending Inspection", "Inspected - Skipped", "Inspected - Complete")).Select
Sheets("Unreleased").Activate
Range("A1").Select
Selection.EntireRow.Delete
End Sub


thanks for your help!

mdmackillop
09-01-2008, 12:29 AM
I would only use the array method if it applies to specific sheets, not all sheets., otherwise see post 5
Sub del()
Sheets(Array("Unreleased", "Pending Inspection", "Inspected - Skipped", "Inspected - Complete")).Select
Rows(1).Select
Selection.ClearContents
Sheets(1).Select
Range("A1").Select
End Sub

shamsam1
09-01-2008, 12:56 AM
small advice

insted of
Selection.EntireRow.Deleteuse this
Selection.ClearContents

As its in arrry it will clear header in respected sheet ,but u neeed to mention sheet anemas correctly

fbastian
09-01-2008, 03:35 AM
Hi all,
thanks for the help.
i got it to work.
thanks mdmackillop vbmenu_register("postmenu_157236", true);
i used post #5 which is what i was looking for.

Shamsam1 thanks for the help, i will keep this array idea for another macro i also need.

thank you

shamsam1
09-01-2008, 03:43 AM
do mark ur thread as solved......