Solved

VBA - SSIS How to remove unwanted rows

Posted on 2014-11-12
3
222 Views
Last Modified: 2014-11-14
I am using SSIS to populate a table from excel. The issue is, at the bottom of the excel sheet there’s some summary information that is not needed and in a different structure to the rest of the spreadsheet, as a result of this when I run my package I get an error.

I have tried creating a script component but can quite get it to work.

The rows are not static. This week I had 1543 rows last week I had 987 rows.

Correct Structure.

ABC123456      21/10/2014      Text      Text      1       32      15/12/2014      7677787      1

Everything after here can be deleted.

Then there is a blank row.
Then 4 columns
‘Type’      ‘Events      Exams’      ‘Text’      ‘Text’
A      10            10      0

Again the row count changes each week.

Then this is repeated two more times with slightly different columns and fields.

What I am trying to do is delete everything  after my first set of data.


Thanks
0
Comment
Question by:aneilg
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40437205
Hi,

pls try

Sub Macro()
RowAfterFirstBlankRow = Range("A1").End(xlDown).End(xlDown).Row
Set myRange = Range(Range("A" & RowAfterFirstBlankRow), _
        Range("A" & Cells.Rows.Count).End(xlUp))
myRange.EntireRow.Delete
End Sub

Open in new window

Regards
0
 

Author Comment

by:aneilg
ID: 40439907
thanks i'll try it later today.
0
 

Author Comment

by:aneilg
ID: 40442139
works fine thanks.


i now need to do the same with another file but with c# code.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question