Copy data from multiple rows until blank then go to the next set of rows until blank

tomtheappraiser

New Member
Joined
Jul 27, 2013
Messages
11
I'm not very educated on VBA yet, so I've been trying to make this spreadsheet work with just formulas, but I think I ran into a show stopper for formulas.
So I'm trying to get data from our assessors office website and put it into my Access DB. So first I'm copying it into an excel sheet then converting it to table format then exporting it to Access. I was doing fine until I got the the buildings section. So the website has a separate page for each building called a "Card". There could be up to 50 buildings, but generally there's an average of 5. On each building they break down each section of the building. So I'm copying the card into a sheet and have multiple copies of these card in that one sheet called "Bldgs", next to each other. So I'm trying to extract all of the building section data into one table on a sheet called "ExBldgSec".
My problem is I never know how many buildings I will have, nor the amount of sections each building will have. So I want it to go down the columns of the first set of rows until there is no information, then I want it to go to the second set of columns (the 2nd card) and copy everything through all of the rows until the rows are blank and repeat the process until it to comes to a set of columns that doesn't have anything in it at which time it will stop copying.
I'm uploading the spreadsheet I'm working on.I don't know how to upload the other sheet I want to copy the data to. As noted the data I want to transfer is under the red outlined columns. I want it transferred to the ExBldgImp tab.
Any help you could provide would be much appreciated. If you have any suggestion on a better way to organize that as well, please make a suggestion,
Thanks again for any help


ImportCountyParcelSample.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAU
1TotalSFTotalBldgsConstTypeYearsBltTotalUnitsUnitTypeZoningCeilingHtFinAreaSF
2
3
4
5Card Number 1Card Number 2Card Number 3Card Number 4
6Locator NumberTax YearCard Number?Total Living Units3F540700Tax YearCard Number?Total Living Units
713F540700202110TotalSF7,442202120TotalSF34,504TotalSF TotalSF 
8Year Built:1960Number of Units:PropType:2Year Built:1955Number of Units:PropType:2PropType:#N/APropType:#N/A
9Building Number:?1Area Under Roof:7,442 ft2SubType Building Number:?2Area Under Roof:34,504 ft2SubType1SubType#N/ASubType#N/A
10Structure Type:RETAIL MULTI OCCUPClass:?CStructure Type:STRIP SHOPPING CNTRClass:?C
11Grade:?D-Identical Units:1Grade:?D-Identical Units:1
12Improvement Name:VACANTImprovement Name:FAITH HOPE MINISTRIES
13
14
15
16LineSectFromToYear BuiltTotal Area?Peri-Use TypeWall HeightWall TypeConstLineSectFromToYear BuiltTotal Area?Peri-Use TypeWall HeightWall TypeConstLineSectFromToYear BuiltTotal Area?Peri-Use TypeWall HeightWall TypeConstLineSectFromToYear BuiltTotal Area?Peri-Use TypeWall HeightWall TypeConst
1711B1B13,721 ft2244SUPPORT12 ftNONEWOOD FRAME, JOIST AND BEAM11B1B1195517,252 ft2606RETAIL STORE12 ftNONEWOOD FRAME, JOIST AND BEAM
1821113,721 ft2244RETAIL STORE12 ftBRICK OR STONE VENEERWOOD FRAME, JOIST AND BEAM2111195517,252 ft2606RETAIL STORE12 ftBRICK OR STONE VENEERWOOD FRAME, JOIST AND BEAM
1931223,721 ft2244MULTI OFFICE12 ftBRICK OR STONE VENEERWOOD FRAME, JOIST AND BEAM
20
21
22
23
24
Bldgs
Cell Formulas
RangeFormula
G7,AQ7,AE7,S7G7=IF(D9="","",LEFT(D9,FIND(" ",D9)-1))
G8,AQ8,AE8,S8G8=VLOOKUP(B10,Lookup!$L$3:$M$100,2,FALSE)
G9,AQ9,AE9,S9G9=IF(VLOOKUP(B10,Lookup!$L$3:$N$100,3,FALSE)="","",VLOOKUP(B10,Lookup!$L$3:$N$100,3,FALSE))
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,482
Messages
5,770,351
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top