VBA Complex Organizational Loop

[ZCapitao]

Hi all

This is to be a monthly task so it needs to be a VBA instead of manually editing the data.
I have data (about 82K rows) that has been extracted from a PDF, the problem is some of the pages have copied the tables into different ranges. For example while pg. X copied into range A:M, pg. Y copied into range C:T.
There are many rows which can be stripped out. But we need to keep the rows without part numbers but do have quantities and values.
I need to keep the rows in the same order because some part numbers were not pulled and will need to be filled in manually later.
Basically what I want is this:

Part_Number, Part_Number_Suffix, Part_Description, Location, Keyword, Product_Category, Min, Max, Quantity_OnHand, Quantity_OnOrder, Value_OnHand,Value_OnOrder

Parts come with three pieces of information in one cell which need to be split out. Part_Number, Part_Suffix, and Part_Description
Part Numbers come with differing suffix values for example Part 6354443 comes in "635443-0 - *" and "635443-97 -*" that need to be stripped out

My first thought is to
For Loop i = Lastrow To 1 Step -1,
Concatenate Ai:Ti and place the value in cell Ai. 'This fixes the tables with differing ranges issue
If Ai contains "Location" Or "Total:" or is blank, delete the entire row
Next i

I would then need to split them all out again and I cannot seem to get that to work.
Any help would be appreciated.

 

[HighAndWilder]

Hi all

This is to be a monthly task so it needs to be a VBA instead of manually editing the data.
I have data (about 82K rows) that has been extracted from a PDF, the problem is some of the pages have copied the tables into different ranges. For example while pg. X copied into range A:M, pg. Y copied into range C:T.
There are many rows which can be stripped out. But we need to keep the rows without part numbers but do have quantities and values.
I need to keep the rows in the same order because some part numbers were not pulled and will need to be filled in manually later.
Basically what I want is this:

Part_Number, Part_Number_Suffix, Part_Description, Location, Keyword, Product_Category, Min, Max, Quantity_OnHand, Quantity_OnOrder, Value_OnHand,Value_OnOrder

Parts come with three pieces of information in one cell which need to be split out. Part_Number, Part_Suffix, and Part_Description
Part Numbers come with differing suffix values for example Part 6354443 comes in "635443-0 - *" and "635443-97 -*" that need to be stripped out

My first thought is to
For Loop i = Lastrow To 1 Step -1,
Concatenate Ai:Ti and place the value in cell Ai. 'This fixes the tables with differing ranges issue
If Ai contains "Location" Or "Total:" or is blank, delete the entire row
Next i

I would then need to split them all out again and I cannot seem to get that to work.
Any help would be appreciated.

Are you able to use XL2BB to post a reasonable amount of data and maybe highlight the different blocks of data that need to be rearranged?