help on VBA code to reset cells to zero

[Ford Barton]

<link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CFord%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Help A Beginner Please....

I am looking for some help on code to reset cells to zero. I have spent a lot of time trying to solve my error on my own and would greatly appreciate any suggestions.
<o> </o>
I have a financial spreadsheet that is used over and over again and needs to conditionally hide unused rows and columns and clear unused cells to zero. I have gotten my VB code to hide the unused rows and columns correctly but I am having difficulties adding the code to clear unused cells to zero. For each row that I hide, I need to zero out 7 columns (E,J,O,T,Y,AD,AI).
<o> </o>
This code worked to hide the unused rows and columns until I tried to zero out the 7 cells in that row using the Range().ClearContents command:
<o> </o>
Sub HUColumns()
BeginColumn = 4
EndColumn = 42
ChkRow = 1

With Worksheets("HISTORICAL FINANCIAL")
For ColCnt = BeginColumn To EndColumn
If .Cells(ChkRow, ColCnt).Value = 1 Then
.Cells(ChkRow, ColCnt).EntireColumn.Hidden = True
Range(RowCnt, 5).ClearContents
Range(RowCnt, 10).ClearContents
Range(RowCnt, 15).ClearContents
Range(RowCnt, 20).ClearContents
Range(RowCnt, 25).ClearContents
Range(RowCnt, 30).ClearContents
Range(RowCnt, 35).ClearContents
Else
.Cells(ChkRow, ColCnt).EntireColumn.Hidden = False
End If
Next ColCnt

BeginRow = 15
EndRow = 40
ChkCol = 1
<o> </o>
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = 1 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End With
End Sub
<o> </o>
<o> </o>
Since the 7 columns are all spaced apart by 4, I also tried adding:
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = 1 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Cells(RowCnt, ChkCol + 4).Select<o></o>
Selection.ClearContents
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End With
<o> </o>
Any help would be greatly appreciated.

Thank you very much.

 

[ravishankar]

Hi
try these codes

dim a as long, b as long
worksheets("("HISTORICAL FINANCIAL").select
for b = 2 to Range("D65536").end(xlup).row
if cells(b,4) = "" then
For a = 5 to 30 step 5
cells(b,a) = ""
next a
rows(b).entirerow.hidden = true
next b
Msgbox "Complete"

if rows in col d is blank, it clears every fifth column and hides those rows and .
Ravi

 

[Ford Barton]

Ravishankar -

Thanks very much for your help. My code was not very efficient, I appreciate your assistance. Many Thanks