Can I do this to excel?

Hi,

I'm not a VB.Net programmer by trade but I have used it in the past for some small projects.

I need to be able to take an Excel spreadsheet and delete all the rows that have nothing on them, or don't contain a number on all the columns i'm interested in.
So basically cleaning up a dump from another program.

Then I need to copy all that information into a new worksheet in an existing Excel workbook.

Then I need to insert a column in an existing worksheet (same workbook) and use a pre-determined set of formula but substitute values in from the new worksheet.

Can this be done using Visual Basic .Net, do I need to buy any extensions, or add-ins? or can you give me some advice on what language I should use.

Thanks,
:Ant

PS: Here is a basic example of what I want to do.

Output from other Accounting package in Excel Format.

Code:

---

(output.xls->main worksheet)
12.95 X1123 C 2005-10-10
12
#
12.95 F0023 D 2005-10-15

---

This would become
(output.xls->new worksheet)

Code:

---

12.95 X1123 C 2005-10-10
12.95 F0023 D 2005-10-15

---

Now we copy that worksheet to the existing workbook so we have it called:
(existing.xls->Oct 2005)

Code:

---

12.95 X1123 C 2005-10-10
12.95 F0023 D 2005-10-15

---

And the existing worksheet that holds the summary for all months needs a new column for the October transactions.. so we need to copy the formula used for September but instead of referencing the Sep 2005 worksheet we will need to reference the Oct 2005 worksheet.

If this is do-able, please let me know.. I would like to get started this week.

Thanks,
:Ant

Yes, I think this can all be done in regular VBA in Excel ... you don't need .Net. You shouldn't need anything else. There have been a lot of posts iterating through rows in a Worksheet, but if you need to see it again, just let us know. What you need to define thoroughly is what constitutes a valid row that you do not want to delete, and it looks like you've done that.

You also will need to know what exactly your formula is going to do. Are you only doing this operation one time, or will this be done every month?

This should get you started. I'm assuming that an empty cell in column D means that the row is invalid ... you'll have to make sure that whatever you decide is definitive for identifying a row as valid or invalid.

Code:

---

Option Explicit
Sub Macro1()

Dim i As Long
Dim aRow As Long
Dim lastRow As Long
Dim aRange As Range
Dim aCell As Range

'You need to know the inclusive range of the source sheet
'This is just for a quick test ...
Set aRange = Range("D1:D7")  '<< this just searches column "D"
lastRow = aRange.Rows.Count  'Find the end row to check

'Iterate through all rows deleting 'invalid' rows
'From your examples,
'  I am assuming that an empty cell in column "D" means an invalid row
aRow = 1
For i = 1 To lastRow
    If IsEmpty(Cells(aRow, "D").Value) Then
        'TEST TEST TEST TEST
        MsgBox Cells(aRow, "D").Address & " is not valid"
        'END TEST
        Rows(aRow).Delete Shift:=xlUp
    Else
        aRow = aRow + 1
    End If
Next i

End Sub

---

Search the Forum for a function "ActualUsedRange" for identifying the real range occupied by data.