Hide columns, rows, and zero out unused cells

Ford Barton

New Member
Joined
Oct 30, 2009
Messages
7
Hello, I have a problem with a VB routine that resets cells to zero. I am an experienced user in Excel (2007, Windows XP) but a novice in VBA. I have spent many hours researching and trying to solve my error on my own and would greatly appreciate any suggestions. 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 getting the code to efficiently clear unused cells to zero.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
Here is the code that I have gotten to work so far:
<o:p> </o:p>
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
Else
.Cells(ChkRow, ColCnt).EntireColumn.Hidden = False
End If
Next ColCnt

BeginRow = 15
EndRow = 40
ChkCol = 1
<o:p> </o:p>
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:p> </o:p>
1) I have to zero out 7 columns and tried in error adding something like this:

Range(RowCnt, 4).ClearContents
Range(RowCnt, 9).ClearContents
Range(RowCnt, 14).ClearContents
Range(RowCnt, 19).ClearContents
Range(RowCnt, 25).ClearContents
Range(RowCnt, 29).ClearContents
Range(RowCnt, 34).ClearContents

2) 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:p></o:p>
Selection.ClearContents
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End With
<o:p> </o:p>
I know this is not right and if it did work, I would still need to reset ChkCol back to 1 before advancing to the next condition.
<o:p> </o:p>
3) My objective is to hide the columns, rows, and clear the data on one worksheet, and then only hide the rows, and clear the data on two other worksheets.
<o:p> </o:p>
Any help would be greatly appreciated. Thank you very much !
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

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
Top