Full Version: Skipping Rows and Deleting Lines
I want to create some VB code to skip two rows and delete the next three lines. Is this accomplished using the Loop code and how would this work?
I would like to send an example of this and add to my question. The attached .xls was converted from a .pdf and has the row headers on highlighted in red in rows 1-5 and then the corresponding data is in rows 6-10. I really only care about the data for rows 1-2 (header) and rows 6-7 (data). After deleting the unwanted rows of header and data, I was to then realign so the record is all on one line so I can do sorting, filtering, etc. I must add that the header rows occur only at the beginning of each .pdf page and there are rows of data after the header (hope that makes sense)
Is this all the data you have or do the rows 6:10 repeat (11:15, 16:20, etc...) and you want to consolidate the five items in question into a database-like sheet setup?
Yes, this spreadsheet is 150 pages long with repeating records like the sample.
Ok, see if the following does the job, if not, please post more of your data...
Martin
CODE
[color="blue"]Sub[/color] Transform()
[color="blue"]Const[/color] NEW_SHEET_NAME [color="blue"]As[/color] [color="blue"]String[/color] = "MyData"
[color="blue"]Dim[/color] lLastRow [color="blue"]As[/color] [color="blue"]Long[/color], lRow [color="blue"]As[/color] [color="blue"]Long[/color]
[color="blue"]Dim[/color] ws [color="blue"]As[/color] Worksheet
[color="blue"]Dim[/color] aCells, i [color="blue"]As[/color] [color="blue"]Long[/color]
[color="blue"]Dim[/color] rg [color="blue"]As[/color] Range
[color="blue"]Set[/color] ws = Sheets("Sheet1") [color="green"]'//source sheet[/color]
lLastRow = ws.Cells(ws.Rows.Count, "A").End(3).Row
[color="green"]'//stack relevant cells here[/color]
aCells = Array("A1", "A2", "C1", "E1", "E2")
[color="blue"]With[/color] Sheets.Add [color="green"]'//target sheet[/color]
[color="blue"]On[/color] [color="blue"]Error[/color] [color="blue"]Resume[/color] [color="blue"]Next[/color]
Application.DisplayAlerts = [color="blue"]False[/color]
Sheets(NEW_SHEET_NAME).Delete
Application.DisplayAlerts = [color="blue"]True[/color]
[color="blue"]On[/color] [color="blue"]Error[/color] [color="blue"]GoTo[/color] 0
.Name = NEW_SHEET_NAME
[color="green"]'//make headers...[/color]
[color="blue"]For[/color] i = [color="blue"]LBound[/color](aCells) [color="blue"]To[/color] [color="blue"]UBound[/color](aCells)
.[A1].Offset(, i) = ws.Range(aCells(i))
[color="blue"]Next[/color] i
[color="green"]'//...and loop through the data range and transform[/color]
[color="blue"]For[/color] lRow = 6 [color="blue"]To[/color] lLastRow [color="blue"]Step[/color] 5
[color="blue"]Set[/color] rg = ws.Cells(lRow, "A").Resize(5, 5)
[color="blue"]With[/color] .Cells(.Rows.Count, "A").End(3)
[color="blue"]For[/color] i = [color="blue"]LBound[/color](aCells) [color="blue"]To[/color] [color="blue"]UBound[/color](aCells)
.Offset(1, i) = rg.Range(aCells(i))
[color="blue"]Next[/color] i
[color="blue"]End[/color] [color="blue"]With[/color]
[color="blue"]Next[/color] lRow
[color="blue"]End[/color] [color="blue"]With[/color]
[color="blue"]End[/color] [color="blue"]Sub[/color]
[color="blue"]Const[/color] NEW_SHEET_NAME [color="blue"]As[/color] [color="blue"]String[/color] = "MyData"
[color="blue"]Dim[/color] lLastRow [color="blue"]As[/color] [color="blue"]Long[/color], lRow [color="blue"]As[/color] [color="blue"]Long[/color]
[color="blue"]Dim[/color] ws [color="blue"]As[/color] Worksheet
[color="blue"]Dim[/color] aCells, i [color="blue"]As[/color] [color="blue"]Long[/color]
[color="blue"]Dim[/color] rg [color="blue"]As[/color] Range
[color="blue"]Set[/color] ws = Sheets("Sheet1") [color="green"]'//source sheet[/color]
lLastRow = ws.Cells(ws.Rows.Count, "A").End(3).Row
[color="green"]'//stack relevant cells here[/color]
aCells = Array("A1", "A2", "C1", "E1", "E2")
[color="blue"]With[/color] Sheets.Add [color="green"]'//target sheet[/color]
[color="blue"]On[/color] [color="blue"]Error[/color] [color="blue"]Resume[/color] [color="blue"]Next[/color]
Application.DisplayAlerts = [color="blue"]False[/color]
Sheets(NEW_SHEET_NAME).Delete
Application.DisplayAlerts = [color="blue"]True[/color]
[color="blue"]On[/color] [color="blue"]Error[/color] [color="blue"]GoTo[/color] 0
.Name = NEW_SHEET_NAME
[color="green"]'//make headers...[/color]
[color="blue"]For[/color] i = [color="blue"]LBound[/color](aCells) [color="blue"]To[/color] [color="blue"]UBound[/color](aCells)
.[A1].Offset(, i) = ws.Range(aCells(i))
[color="blue"]Next[/color] i
[color="green"]'//...and loop through the data range and transform[/color]
[color="blue"]For[/color] lRow = 6 [color="blue"]To[/color] lLastRow [color="blue"]Step[/color] 5
[color="blue"]Set[/color] rg = ws.Cells(lRow, "A").Resize(5, 5)
[color="blue"]With[/color] .Cells(.Rows.Count, "A").End(3)
[color="blue"]For[/color] i = [color="blue"]LBound[/color](aCells) [color="blue"]To[/color] [color="blue"]UBound[/color](aCells)
.Offset(1, i) = rg.Range(aCells(i))
[color="blue"]Next[/color] i
[color="blue"]End[/color] [color="blue"]With[/color]
[color="blue"]Next[/color] lRow
[color="blue"]End[/color] [color="blue"]With[/color]
[color="blue"]End[/color] [color="blue"]Sub[/color]
Martin
This code worked real good -- a few minor changes, but otherwise it was extremely helpful. Thanks so much.
You're welcome
M.
M.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.