VBA code to automatically hide blank rows but need to unhide when needed to enter details.

[DeepuSultan]

Hi Team,

I am building an excel template where there are 250 blank rows (as of now) and I need help in automatically hiding the blank rows so that my team working on it can see the last row where the totals are. I don't want everytime to scroll to the last row for the totals. For example if they need to fill only the first 10 rows then want the remaining blank rows to get automatically hidden and just show up the last row of totals after feeling up the first 10 rows. The point to note here is that I have formulas in some of the columns to calculate discounts and sums, what I meant to say that the whole rows will not be blank. Some of the cells in the rows will have formulas.

Also I want if H1>Sheet1!E3 then hide the following columns l, o, r, u and if H1>Sheet1!E4 then hide the following columns j, m, p, s.

 

[offthelip]

I you put this code in the worksheet activate adn worksheet change evetns it should hopefuylly do what you want:

Private Sub Worksheet_Activate()
Range(Cells(1, 1), Cells(100000, 1)).EntireRow.Hidden = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range("A1:A" & lastrow)
For i = 1 To lastrow
 If inarr(i, 1) = "" Then Exit For
Next i
Range(Cells(i + 1, 1), Cells(lastrow - 1, 1)).EntireRow.Hidden = True
    

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Range(Cells(1, 1), Cells(100000, 1)).EntireRow.Hidden = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range("A1:A" & lastrow)
For i = 1 To lastrow
 If inarr(i, 1) = "" Then Exit For
Next i
Range(Cells(i + 1, 1), Cells(lastrow - 1, 1)).EntireRow.Hidden = True
    

End Sub

 

[DeepuSultan]

Also I want if H1>Sheet1!E3 then hide the following columns l, o, r, u and if H1>Sheet1!E4 then hide the following columns j, m, p, s.

what about this?

 

[DeepuSultan]

T

I you put this code in the worksheet activate adn worksheet change evetns it should hopefuylly do what you want:

Private Sub Worksheet_Activate()
Range(Cells(1, 1), Cells(100000, 1)).EntireRow.Hidden = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range("A1:A" & lastrow)
For i = 1 To lastrow
 If inarr(i, 1) = "" Then Exit For
Next i
Range(Cells(i + 1, 1), Cells(lastrow - 1, 1)).EntireRow.Hidden = True
   

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Range(Cells(1, 1), Cells(100000, 1)).EntireRow.Hidden = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range("A1:A" & lastrow)
For i = 1 To lastrow
 If inarr(i, 1) = "" Then Exit For
Next i
Range(Cells(i + 1, 1), Cells(lastrow - 1, 1)).EntireRow.Hidden = True
   

End Sub

This is not working. I don't know why

 

[offthelip]

You didn't give any specific details about your worksheet so I made a number of assumptions ( which is also why i didn't answer the second question)
What I did was on sheet 1 of a blank workbook : I put some text in the cells A1 to A10, I then put text in A250 ( the last row)
I then put the two bits of code in the code for that worksheet
I then selected sheet 2 and then back to sheet 1: rows 1 to 11 appear and then all rows are hidden until row 250.
I then typed into A11 and when I hit enter row 12 appears
I sugggest you do exactly the same on a blank workbook and see if that works.

 

[DeepuSultan]

You didn't give any specific details about your worksheet so I made a number of assumptions ( which is also why i didn't answer the second question)
What I did was on sheet 1 of a blank workbook : I put some text in the cells A1 to A10, I then put text in A250 ( the last row)
I then put the two bits of code in the code for that worksheet
I then selected sheet 2 and then back to sheet 1: rows 1 to 11 appear and then all rows are hidden until row 250.
I then typed into A11 and when I hit enter row 12 appears
I sugggest you do exactly the same on a blank workbook and see if that works.

This is the template,

Pricing and Margin file template-1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1				Average Days		755.85	1095
2	License	Original SKU	Quantity	Expiry Date	Co-term Date	Co-Term Days	Weighted Avg Days	LP 1Yr	LP 3Yr	LP 5Yr	Price with 1Y LP	Price with 3Y LP	Price with 5Y LP	Per unit cost for 1Y	Per unit cost for 3Y	Per unit cost for 5Y	Margin Cost for 1Y LP	Margin Cost for 3Y LP	Margin Cost for 5Y LP
3	ABCDFgbGHJJK	ABC123	30	7/10/2024	3/31/2027	995	29850	1233	2466	3699	33611.92	67223.84	100835.75	91.70	212.94	258.98	7499.30	5804.80	4235.92
4	DEFGjglKLLJHGFDF	DEF123	5	10/26/2024	3/31/2027	887	4435	1233	2466	3699	4993.93	9987.86	14981.79	91.70	212.94	258.98	1114.22	862.46	629.36
5	GHIGHGJJJUIKKd	GHI123	11	2/16/2027	3/31/2027	44	484	1095	2191	3287	484.00	968.44	1452.88	91.70	212.93	258.97	121.60	94.12	68.68
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51			46				34769		Total Value			78,180.14
52						Discount	66.75%		Customer Price			25,994.90
53									Margin$			25,994.90
54									Margin%			100.00%
Pricing+Margin Template

Cell Formulas
Range	Formula
F1	F1	=ROUND(G51/C51,2)
G1	G1	=IF(AND(F1>Sheet1!E4,F1<Sheet1!D4),1825,IF(AND(F1>Sheet1!D3,F1<Sheet1!E4),1095,IF(AND(F1>Sheet1!E3,F1<Sheet1!D3),1095,365)))
Q3:Q50	Q3	=IF(OR(C3="",N3=""),"",(C3*N3*$F3/365))
R3:R50	R3	=IF(OR(C3="",O3=""),"",(C3*O3*$F3/1095))
S3:S50	S3	=IF(OR(C3="",P3=""),"",(C3*P3*$F3/1825))
M3:M6	M3	=IF(OR(C3="",I3=""),"",(C3*J3*$F3/$G$1))
M9,M48,M45,M42,M39,M36,M33,M30,M27,M24,M21,M18,M15,M12	M9	=IF(OR(C9="",I9=""),"",(C9*I9*$F9/$G$1))
F3:F50	F3	=IF(OR(D3="",E3=""),"",E3-D3+1)
K3:K50	K3	=IF(OR(C3="",H3=""),"",(C3*H3*$F3/$G$1))
C51,L51,G51	C51	=SUM(C3:C50)
G3:G50	G3	=IF(OR(C3="",F3=""),"",C3*F3)
L3:L50	L3	=IF(OR(C3="",I3=""),"",(C3*I3*$F3/$G$1))
L52	L52	=L51*(1-G52)
L53	L53	=L52-R51
L54	L54	=L53/L52

 

[DeepuSultan]

I could not copy 250 rows for limitations with Xl2bb, hence have includedonly 48 rows. But in actual my file would have 250 rows to fill i.e from row# 3-252. There are two headers Row #1 and 2 and four rows #253-256 which have totals at the bottom. There is also a reference from another sheet named "Sheet1" from the same workbook in G1.

What I need is-

1. when I fill up Row#3 (or any no of rows) and go to another sheet (come out of that working sheet), the remaining blank rows should get hidden automatically, leaving only blank row open (prbably the next blank row) so that I could start entering details again. This would keep repeating untill row# 252 is filled up.

2. what I also need is that if the value in F1>Sheet1!E3 then hide the following columns L, O, R, U but keep other columns visible. Again if the value in F1>Sheet1!E4 then hide the following columns J, M, P, S and keep other columns visible.

Could you help me please

 

[offthelip]

Here is the solution to question 1 which is just a couple of modificatiosn to my code to take account of your worksheet:

Private Sub Worksheet_Activate()
Range(Cells(1, 1), Cells(100000, 1)).EntireRow.Hidden = False
lastrow = Cells(Rows.Count, "I").End(xlUp).Row   ' changed to I
inarr = Range("A1:A" & lastrow)
For i = 2 To lastrow - 4                        ' change to 2 to lastrow -4
 If inarr(i, 1) = "" Then Exit For
Next i
Range(Cells(i + 1, 1), Cells(lastrow - 4, 1)).EntireRow.Hidden = True  ' changed to lastrow -4
    

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Range(Cells(1, 1), Cells(100000, 1)).EntireRow.Hidden = False
lastrow = Cells(Rows.Count, "I").End(xlUp).Row
inarr = Range("A1:A" & lastrow)
For i = 2 To lastrow - 4
 If inarr(i, 1) = "" Then Exit For
Next i
Range(Cells(i + 1, 1), Cells(lastrow - 4, 1)).EntireRow.Hidden = True
    

End Sub

 

[offthelip]

2. what I also need is that if the value in F1>Sheet1!E3 then hide the following columns L, O, R, U but keep other columns visible. Again if the value in F1>Sheet1!E4 then hide the following columns J, M, P, S and keep other columns visible.

What happens if the value if F1 is greater that Sheet1!E3 and greater than Sheet1!E4??
also does that value in F1 change because of a data entry by the user or will it change due to a calculation, i.e I am asking what is going to trigger showing and hiding the columns