Hide cells below a certain cell

[mellowe]

Hello..
Here's the problem. I would like to hide a selection of cells in a
column after the occurrence of a certain cell. eg. in column A if a
cell contains 'MyCell' hide the cells below this. To further this
problem i need those cells to be copied into the adjacent column cells
eg. column B.
So If A3 contained 'MyCell' the cells below this cell would be hidden
and the values be copied to B4. The reason why I can't have them cut
and copied to column B is that column A has connecting formulas.
I cant seem to work out how to hide some of the cells in a column most
macros etc. refer to hiding full columns.
Please help!

 

[Dave Peterson]

You can hide a column or a row--but you can't hide just one cell.

But you can play games with the format.

You could give the cell(s) a custom format of:
;;;
(3 semicolons)
and the cell will look empty.

You could also use format|conditional formatting to give the cell the same color
font as the fill color (white on white)?

Does that help?

 

[mellowe]

Hi there Dave!

Yeah I had seen the custom format thing with the ;;; but I was unsure
how to do this! As again I have a large macro and need to incorporate
this into my VB code! And the change cell colour to the background was
also a suggestion I had seen posted in an earlier topic - I can do this
manually at the end but again I am not sure how to put this in my code!

 

[Dave Peterson]

Maybe this'll give you an idea:

Option Explicit
Sub testme02()

Dim FoundCell As Range
Dim rng As Range
Dim WhatToFind As String

WhatToFind = "MyCell"

With Worksheets("sheet1")
With .Range("a:a")
Set FoundCell = .Cells.Find(What:=WhatToFind, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
'do nothing
MsgBox "Not found"
Exit Sub
End If

Set rng = .Range(FoundCell.Offset(1, 0), _
.Cells(.Rows.Count, "A").End(xlUp))

rng.Offset(0, 1).FormulaR1C1 = "=rc[-1]"
rng.NumberFormat = ";;;"

End With

End Sub

 

[mellowe]

Thanks again! , I can use this but is there an inclusion I can use so
that I can still hide the cells under the cell that contains 'MyCell'
UNTIL I hit the next occurrence of 'MyCell'?

 

[Dave Peterson]

Option Explicit
Sub testme02()

Dim FoundCell1 As Range
Dim FoundCell2 As Range
Dim rng As Range
Dim WhatToFind As String

WhatToFind = "MyCell"

With Worksheets("sheet1")

With .Range("a:a")
If Application.CountIf(.Cells, "*" & WhatToFind & "*") < 2 Then
MsgBox "not enough to find!"
Exit Sub
End If

Set FoundCell1 = .Cells.Find(What:=WhatToFind, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set FoundCell2 = .FindNext(FoundCell1)

End With
Set rng = .Range(FoundCell1.Offset(1, 0), _
FoundCell2.Offset(-1, 0))

rng.Offset(0, 1).FormulaR1C1 = "=rc[-1]"
rng.NumberFormat = ";;;"

End With

End Sub

 

[mellowe]

Once again thanks for that! Will be able to work with this!