Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not deleting headers
as an aswer to a previous question I've received the following code:
Dim c, sh For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula Then c.ClearContents Next c End If Next sh this works fine. The only problem being that row 1 shouldn't be deleted as it is the header. How can I do that? thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not deleting headers
Hi Mauro,
Try: Sub Tester() Dim c As Range Dim sh As Worksheet For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula And c.Row 1 _ Then c.ClearContents Next c End If Next sh End Sub --- Regards, Norman "Mauro" wrote in message .. . as an aswer to a previous question I've received the following code: Dim c, sh For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula Then c.ClearContents Next c End If Next sh this works fine. The only problem being that row 1 shouldn't be deleted as it is the header. How can I do that? thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not deleting headers
Think this will need to be inseted in your code.
With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" End With "Norman Jones" wrote: Hi Mauro, Try: Sub Tester() Dim c As Range Dim sh As Worksheet For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula And c.Row 1 _ Then c.ClearContents Next c End If Next sh End Sub --- Regards, Norman "Mauro" wrote in message .. . as an aswer to a previous question I've received the following code: Dim c, sh For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula Then c.ClearContents Next c End If Next sh this works fine. The only problem being that row 1 shouldn't be deleted as it is the header. How can I do that? thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not deleting headers
Hi Mauro,
Additionally, in the first line of your macro, I would change Sheets to Worksheets, i.e. change: For Each sh In ThisWorkbook.Sheets to For Each sh In ThisWorkbook.Worksheets Otherwise the code will engender an error if the workbook contains chart or dialog sheets. --- Regards, Norman "Norman Jones" wrote in message ... Hi Mauro, Try: Sub Tester() Dim c As Range Dim sh As Worksheet For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula And c.Row 1 _ Then c.ClearContents Next c End If Next sh End Sub --- Regards, Norman "Mauro" wrote in message .. . as an aswer to a previous question I've received the following code: Dim c, sh For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula Then c.ClearContents Next c End If Next sh this works fine. The only problem being that row 1 shouldn't be deleted as it is the header. How can I do that? thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not deleting headers
Hi David.
"David" wrote in message ... Think this will need to be inseted in your code. With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" End With I think not - unless I have misunderstood Mauro's post! --- Regards, Norman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not deleting headers
Think you are right Norm, misread it.
"Norman Jones" wrote: Hi David. "David" wrote in message ... Think this will need to be inseted in your code. With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" End With I think not - unless I have misunderstood Mauro's post! --- Regards, Norman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not deleting headers
Think you are right
"Norman Jones" wrote: Hi David. "David" wrote in message ... Think this will need to be inseted in your code. With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" End With I think not - unless I have misunderstood Mauro's post! --- Regards, Norman |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not deleting headers
It works, it works.
The only thing is the speed. It does the whole thing but it does it increadible slowly "Norman Jones" wrote in message ... Hi Mauro, Try: Sub Tester() Dim c As Range Dim sh As Worksheet For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula And c.Row 1 _ Then c.ClearContents Next c End If Next sh End Sub --- Regards, Norman "Mauro" wrote in message .. . as an aswer to a previous question I've received the following code: Dim c, sh For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula Then c.ClearContents Next c End If Next sh this works fine. The only problem being that row 1 shouldn't be deleted as it is the header. How can I do that? thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not deleting headers
Hi Mauro,
It works, it works. The only thing is the speed. It does the whole thing but it does it increadible slowly Try inserting: Application.ScreenUpdating = False after the last Dim line and insert Application.ScreenUpdating = True as the last line. --- Regards, Norman |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not deleting headers
Less elegent, but faster...copy the headers to a variant, clear all
constants, then put back the headers.... Sub Tester() Dim c As Range Dim sh As Worksheet Dim headers As Variant For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then headers = sh.Rows(1) sh.UsedRange.SpecialCells(xlCellTypeConstants).Cle arContents sh.Rows(1) = headers End If Next sh End Sub "Mauro" wrote: It works, it works. The only thing is the speed. It does the whole thing but it does it increadible slowly "Norman Jones" wrote in message ... Hi Mauro, Try: Sub Tester() Dim c As Range Dim sh As Worksheet For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula And c.Row 1 _ Then c.ClearContents Next c End If Next sh End Sub --- Regards, Norman "Mauro" wrote in message .. . as an aswer to a previous question I've received the following code: Dim c, sh For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula Then c.ClearContents Next c End If Next sh this works fine. The only problem being that row 1 shouldn't be deleted as it is the header. How can I do that? thanks |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not deleting headers
OK, when I gave you the first code, didn't think that the usedRange will
be so large (as it now seems from your post.). Clearing content of each cell then moving to next will definately take too long. Therefore I modified the code to set a Union of all cells having no formula (and not in the first row) and then in one shot clear the contents. Use below code. I hope you will find it ultraFast compared to the time it must be taking with present code. Sub FastClear() Dim uRange As Range, clRange As Range Dim c, sh For Each sh In ThisWorkbook.Worksheets If sh.Name < "Main_Sheet_Name_Here" Then With sh.UsedRange Set uRange _ = .Range(.Cells(2, 1), .Cells(.Rows.Count, .Columns.Count)) 'This has set the range excluding row 1 Set clRange = .Cells(.Rows.Count + 1, 1) 'an unused cell is assigned to clRange 'so that it can be used in Union method End With Set clRange = uRange.Cells(1, 1) For Each c In uRange.Cells If Not c.HasFormula Then Set clRange = Union(clRange, c) End If Next c clRange.ClearContents End If Next sh End Sub Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not deleting headers
Now, that's so straight forward.
Never looked at SpecialCells method. Guess I need to look in to it seriously, looks like will definately help in cutting my codes short. Sharad "Patrick Molloy" wrote in message ... Less elegent, but faster...copy the headers to a variant, clear all constants, then put back the headers.... Sub Tester() Dim c As Range Dim sh As Worksheet Dim headers As Variant For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then headers = sh.Rows(1) sh.UsedRange.SpecialCells(xlCellTypeConstants).Cle arContents sh.Rows(1) = headers End If Next sh End Sub "Mauro" wrote: It works, it works. The only thing is the speed. It does the whole thing but it does it increadible slowly "Norman Jones" wrote in message ... Hi Mauro, Try: Sub Tester() Dim c As Range Dim sh As Worksheet For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula And c.Row 1 _ Then c.ClearContents Next c End If Next sh End Sub --- Regards, Norman "Mauro" wrote in message .. . as an aswer to a previous question I've received the following code: Dim c, sh For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula Then c.ClearContents Next c End If Next sh this works fine. The only problem being that row 1 shouldn't be deleted as it is the header. How can I do that? thanks |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not deleting headers
it works perfectly now, thanks to you all for your help
"Mauro" wrote in message .. . as an aswer to a previous question I've received the following code: Dim c, sh For Each sh In ThisWorkbook.Sheets If sh.Name < "Main_Sheet_Name_Here" Then For Each c In sh.UsedRange If Not c.HasFormula Then c.ClearContents Next c End If Next sh this works fine. The only problem being that row 1 shouldn't be deleted as it is the header. How can I do that? thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup using column headers and row headers | Excel Discussion (Misc queries) | |||
Deleting Headers and Footers that are in the drop down Menu? | Excel Discussion (Misc queries) | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel | |||
Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming |