Auto unhide one row --- OR --- Auto insert one row

[Bkisley]

I need to show the following and I can not figure this out!

Scenario 1
I have row 10 filled with data up to column O
I have rows 11-24 hidden
This 15 row set up is copied down 50 times so the below process would need to be repeated in each of those 15 row sets
If column O has a value >0 than I need row 11 to unhide while keeping rows 12-24 still hidden.

To help visualize this
row 10 component name and initial manufacturing process
row 11-24 component secondary manufacturing processes - initially hidden then for each value > 0 in column O one row at a time is unhidden
row 25 component name and initial manufacturing process
row 26-39 component secondary manufacturing processes - initially hidden then for each value > 0 in column O one row at a time is unhidden
and so on....

I don't want 15 rows showing for every component when some components may only have one or two processes


Scenario 2 is less desirable but I could work with it
The same story as above, however rows are not hidden

I have row 10 filled with data up to column O
If column O has a value >0 than I need a row inserted below row 5


PLEASE PLEASE HELP!

 

[rpaulson]

this may help.

this will hide rows in column O that are not > 0

change range as needed

Sub hide_Me()

For Each cell In Range("O10:O20")
r = cell.Row
Rows(r).Hidden = True
If cell.Value > 0 Then Rows(r).Hidden = False
Next cell



End Sub

hth,

Ross

 

[Bkisley]

I don't believe this is the fix. I need this to go as I type in data, not a fix to data already entered. If I enter a value in say cell O10 I want row 11 and only 11 to be unhidden OR​ Row 11 to be inserted

 

[rpaulson]

so if you are enter something in O10 then row 11 will unhide. if you enter something in O11 then row 12 will unhide? etc...

 

[Bkisley]

Exactly! Yes that is what I need

 

[rpaulson]

ther is probably a better way, but this should work,

change the range in 2 spots as needed.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("O10:O400")) Is Nothing Then

Application.ScreenUpdating = False
For Each cell In Range("O10:O400")
r = cell.Row
'Rows(r).Hidden = True
If cell.Value > 0 Then
Rows(r).Hidden = False
Rows(r + 1).Hidden = False

Else
Rows(r + 1).Hidden = True
End If
Next cell

End If
End If
Application.ScreenUpdating = True
End Sub

hth,

Ross

 

[Bkisley]

hmm this kind of works!
This hides all the rows from 11-400. I will have to unhide all the rows then when I need to add another component which would not be efficient. I think the best way is to avoid dealing with the whole hide unhide features and just insert a row.
Could you let me know what VBA code I would need to do for the following...
If I type in a value in column O I want a row to automatically be inserted below that row. Say I am typing across columns in row 10. Once I put in a value in column O I want a line to be automatically inserted.

 

[Bkisley]

Auto insert row after typing in a certain value

Hello! I have a problem that I can not seem to find a solution for.

If I am typing in row 10 and inputting data in rows A through D I want excel to automatically insert a row below this data line (row 10) if cell E10 >0

Example data
A10 = first data point B10 = 2nd data point C10 = 3rd data point D10 = 4th data point E10 = ((A10+B10)/C10)*D10
So if the value of E10 > 0 a row would automatically be inserted below this row creating a new row 11
If the value of E10 < or = to 0 then don't do anything

Please tell me this is possible!
Thank you!

 

[Macropod]

Re: Auto insert row after typing in a certain value

Bkisley, as per Forum rule 12 (https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html), kindly don't ask questions on the same topic in multiple threads. I have merged your latest question into the original thread.

 

[lhartono]

Re: Auto insert row after typing in a certain value

Try this and just remove the 2nd IF (pink) for automatic formula insertion if you dont want.

[COLOR=#0000cd]Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Column = 4) And (Not IsEmpty(Target)) Then
[/COLOR][COLOR=#ee82ee]    If IsEmpty(Target.Offset(, 1)) And (Not Target.Offset(, 1).HasFormula) Then
        Target.Offset(, 1).Formula = "=((RC[-4]+RC[-3])/RC[-2])*RC[-1]"
    End If[/COLOR][COLOR=#0000cd]
    If (Not IsEmpty(Target.Offset(, 1).Value)) Then
        If Target.Offset(, 1).Value > 0 Then
            Application.EnableEvents = False
            Rows(Target.Row + 1 & ":" & Target.Row + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Application.EnableEvents = True
        End If
    End If
End If[/COLOR]

[COLOR=#0000cd]End Sub[/COLOR]