Stumped on How to remove unwanted lines

[tassie]

gmroi.xls
	A	B	C	D	E	F	G	H
1
2
3
4	11/11/2005			GL	Gunns L	imited
5	9:00:51	Produ	ct Sta	tus by	Warehous	e - Compac	t (Menu IB	1)
6	----------	-------------------------------------	------	------	---------	----------	----------	-----------
7					Stock	Reserved	Customer	Current
8	Product	Description Ve	ndor S	tatus	Onhand	Sales	Back Order	Available
9					Qty	Qty	Qty	Qty
10	----------	-------------------------------------	------	------	---------	----------	----------	-----------
11
12
13	100147	BERG JET DRY N/SLIP F/RED 1LT	DULUXA	1	5	0	0	5
14	100152	BERG JET DRY N/SLIP F/GREEN 4L	DULUXA	1	4	0	0	4
15	100153	BERG JET DRY N/SLIP F/GREEN 1L	DULUXA	1	4	0	0	4
16	100170	BERG JET DRY N/SLIP F/RED 4LT	DULUXA	1	4	0	0	4
17	101616	BERG TRUE GRIP #N95 300G	DULUXA	1	3	0	0	3
18	102154	T/P WOODSTAIN BALTIC PINE 4LT	TASPAB	1	2	0	0	2
19	102186	SOLAGARD GLOSS DTB 10LTR	WATTYA	1	2	0	0	2
20	102612	ID GLS ENAMEL WHITE 4LT	WATTYA	1	2	0	0	2
21	102615	ID UNDERCOAT MULTI PURP 4 LT	WATTYA	1	6	0	1	5
22	102689	T/P WOODSTAIN MAHOGANY 500ML	TASPAB	1	4	0	0	4
23	102690	T/P WOODSTAIN BALTIC PINE 500M	TASPAB	1	4	0	0	4
24	102691	T/P WOODSTAIN WALNUT 500ML	TASPAB	1	6	0	0	6
25	102692	T/P WOODSTAIN CEDAR 500ML	TASPAB	1	3	0	0	3
26	102779	T/P THINNERS ROAD MARKING 4L	TASPAB	1	2	0	0	2
27	103280	FILLER HEAVY DUTY RTU ACCENT	MITREB	1	1	0	0	1
28	103281	FILLER FINE SURFACE RTU ACCENT	MITREB	1	6	0	0	6
29	103282	FILLER FLEXI WOOD RTU ACCENT	MITREB	1	7	-1	0	8
IB1_status

Hi Guys,

I have several reports which are imported into Excel.
The challenge I am having is getting rid off the lines between each page
so that I am only using data.

I have tried using filters with not much success.

Is there a way with a function that can detect if cells in column A are
numeric if so retain that line otherwise delete it?

Appreciate any feedback

Thanks & Regards

Tassie

 

[jag108]

I frequently do what you are doing.

I have written a small excerpt of the code I use, you will need to adjust to suit your needs.

I will help further where I can.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()
<SPAN style="color:#00007F">Set</SPAN> TestRange = Intersect(Range("A:A"), ActiveSheet.UsedRange)
lastrowsrc = TestRange.Cells(TestRange.Cells.Count).Row <SPAN style="color:#007F00">'get last row number in the range</SPAN>
firstrowsrc = TestRange.Cells(1).Row <SPAN style="color:#007F00">'get the first row number in the range</SPAN>
<SPAN style="color:#007F00">'Collect the required data from the specific cells by looping through the rows</SPAN>
<SPAN style="color:#00007F">For</SPAN> I = lastrowsrc <SPAN style="color:#00007F">To</SPAN> firstrowsrc <SPAN style="color:#00007F">Step</SPAN> -1
    Cells(lastrowsrc, 1).Select
    <SPAN style="color:#00007F">If</SPAN> ActiveCell.Value = "What ever" <SPAN style="color:#00007F">Or</SPAN> ActiveCell.Value = Emtpy <SPAN style="color:#00007F">Or</SPAN> ActiveCell.Value = "anything" <SPAN style="color:#00007F">Then</SPAN>
        EntireRow.Delete
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[tassie]

Hi jag108 ,

Thanks for your solution and the offer of
further assistance if needed.


Tassie

 

[gwkenny]

Cosmetic surgery.

Sorry, couldn't resist.

If you only do this occasionally (which I don't think you do), you could add another column and insert incremental numbers from 1 to whatever. For example in Column A.

Then you sort on the column of numeric data. All the non-numeric stuff like titles and dashes go to the top or bottom depending if you chose ascending or descending sort order. Then you can delete these extraneous lines. Re-sort on Column A to get the data in the original order.

 

[tassie]

Hi jag108,


Sheets("IB1_status").Select
Set TestRange = Intersect(Range("A:A"), ActiveSheet.UsedRange)
lastrowsrc = TestRange.Cells(TestRange.Cells.Count).Row 'get last row number in the range
firstrowsrc = TestRange.Cells(1).Row 'get the first row number in the range
'Collect the required data from the specific cells by looping through the rows

Range("A1").Select
ActiveCell = firstrowsrc
Range("A2").Select
ActiveCell = lastrowsrc

For I = lastrowsrc To firstrowsrc Step -1
Cells(lastrowsrc, 1).Select
If ActiveCell.Value = "" Then ' testing for spaces
EntireRow.Delete
End If
Next
End Sub

Hve included some lines for testing
Bold line entire row delete displays debug.
Runtime erroe 424 object required.

Tassie.

 

[gwkenny]

Replace "EntireRow.Delete" with "ActiveCell.EntireRow.Delete".

OR

You could add "ActiveCell" before "EntireRow.Delete" to get "ActiveCell.EntireRow.Delete".

OR

You could erase "EntireRow" and replace it with "ActiveCell.EntireRow" to get "ActiveCell.EntireRow.Delete".

Any of the above, or any other combination that will get you to "ActiveCell.EntireRow.Delete" should solve your problem