Remove empty rows and rearrange num column

[motilulla]

Hello,</SPAN></SPAN>

I need to remove empty rows from column C and rearrange Num column B </SPAN></SPAN>

Original Data</SPAN></SPAN>

Book1
	A	B	C	D	E
1
2
3
4
5	Seriol	Num	Data
6	1	1	K-K-K-K-K-K-K-K-K-K-K-K-K-K
7	2	2	K-K-K-K-K-K-K-K-K-K-K-K-K-L
8	3	3
9	4	4	K-K-K-K-K-K-K-K-K-K-K-K-L-K
10	5	5	K-K-K-K-K-K-K-K-K-K-K-K-L-L
11	6	6
12	7	7	K-K-K-K-K-K-K-K-K-K-K-K-25-K
13	8	8
14	9	9	K-K-K-K-K-K-K-K-K-K-K-K-25-25
15	10	10
16	11	11
17	12	12
18	13	13	K-K-K-K-K-K-K-K-K-K-K-L-L-K
19	14	14
20	15	15
21	16	16	K-K-K-K-K-K-K-K-K-K-K-L-25-K
22	17	17
23	18	18
24	19	19
25	20	20	K-K-K-K-K-K-K-K-K-K-K-25-K-L
26	21	21	K-K-K-K-K-K-K-K-K-K-K-25-K-25
27	22	22	K-K-K-K-K-K-K-K-K-K-K-25-L-K
28	23	23	K-K-K-K-K-K-K-K-K-K-K-25-L-L
29	24	24
30	25	25
31	26	26	K-K-K-K-K-K-K-K-K-K-K-25-25-L
32	27	27	K-K-K-K-K-K-K-K-K-K-K-25-25-25
33	28	28
34	29	29
35	30	30
36	31	31
37	32	32
38	33	33
39	34
40	35
41	36
42	37
43	38
44	39
45	40
46	41
47	42
48
49
Data

Required this Result </SPAN></SPAN>

Book1
	A	B	C	D	E
1
2
3
4
5	Seriol	Num	Data
6	1	1	K-K-K-K-K-K-K-K-K-K-K-K-K-K
7	2	2	K-K-K-K-K-K-K-K-K-K-K-K-K-L
8	3	3	K-K-K-K-K-K-K-K-K-K-K-K-L-K
9	4	4	K-K-K-K-K-K-K-K-K-K-K-K-L-L
10	5	5	K-K-K-K-K-K-K-K-K-K-K-K-25-K
11	6	6	K-K-K-K-K-K-K-K-K-K-K-K-25-25
12	7	7	K-K-K-K-K-K-K-K-K-K-K-L-L-K
13	8	8	K-K-K-K-K-K-K-K-K-K-K-L-25-K
14	9	9	K-K-K-K-K-K-K-K-K-K-K-25-K-L
15	10	10	K-K-K-K-K-K-K-K-K-K-K-25-K-25
16	11	11	K-K-K-K-K-K-K-K-K-K-K-25-L-K
17	12	12	K-K-K-K-K-K-K-K-K-K-K-25-L-L
18	13	13	K-K-K-K-K-K-K-K-K-K-K-25-25-L
19	14	14	K-K-K-K-K-K-K-K-K-K-K-25-25-25
20	15
21	16
22	17
23	18
24	19
25	20
26	21
27	22
28	23
29	24
30	25
31	26
32	27
33	28
34	29
35	30
36	31
37	32
38	33
39	34
40	35
41	36
42	37
43	38
44	39
45	40
46	41
47	42
48
49
Result

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN>
Moti</SPAN></SPAN>

 

[footoo]

In your originala data, put in B6 and fill down

=IF(C6<>"",COUNTA($C$6:C6),"")

 

[Rick Rothstein]

Here is a macro that does what I think you want...

[table="width: 500"]
[tr]
	[td]Sub RemoveBlanksInColumnCandRenumberColumnB()
  On Error Resume Next
  Intersect(Range("C6", Cells(Rows.Count, "B").End(xlUp).Offset(, 1)).SpecialCells(xlBlanks).EntireRow, Columns("B:C")).Delete xlShiftUp
  With Range("B6", Cells(Rows.Count, "C").End(xlUp).Offset(, -1))
    .Value = Evaluate("ROW(1:" & .Rows.Count & ")")
  End With
  On Error GoTo 0
End Sub[/td]
[/tr]
[/table]

 

[motilulla]

Here is a macro that does what I think you want...

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Sub RemoveBlanksInColumnCandRenumberColumnB()
  On Error Resume Next
  Intersect(Range("C6", Cells(Rows.Count, "B").End(xlUp).Offset(, 1)).SpecialCells(xlBlanks).EntireRow, Columns("B:C")).Delete xlShiftUp
  With Range("B6", Cells(Rows.Count, "C").End(xlUp).Offset(, -1))
    .Value = Evaluate("ROW(1:" & .Rows.Count & ")")
  End With
  On Error GoTo 0
End Sub
[/TD]
[/TR]
</TBODY>[/TABLE]

Rick, the macro worked great as request</SPAN></SPAN>

I appreciate your kind help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>