Txt Import problems.....
Hi Guys,
I have made a macro which imports data from a text file. The macro works pretty sweetly and sorts data and then removes various rows which I dont want via functions of CountIf and CurrentRegion. Herein lies my problem. The last few rows of my data look like: A B C D E F 99 ABC DEF HIG LMO PQR STU 101 *** VEH REP *** 102 *** 23/11 03 *** 103 *** ***** ** *** 104 105 DIST1 106 107 DIST2 108 109 DIST3 Current Region only selects down as far as the last row of "*" because the next line is entirely blank. I could just calculate end of current region and make a selection where the start row is "-4" and end row is +1000 (to be safe) but this is rather ugly and I like to have robust code. I guess what I need is something to find the first blank row in column A then delete rows until it gets two consecutive blank rows, then stop? Anyone help? Cheers -Al |
Txt Import problems.....
This is one method Sub abc() Dim i As Integer For i = Range("a65536").End(xlUp).Row To 1 Step -1 If Range("a" & i).Value = "" Then Rows(i).Delete Shift:=xlUp End If Next i End Su ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
Txt Import problems.....
Instead of using Current Region to find the range with data you can use
UsedRange, e.g.: ActiveSheet.UsedRange.Select If you still want to delete the empty rows a quick and easy way is to copy the rows with data to a new sheet like this: Union(ActiveSheet.UsedRange.SpecialCells(xlConstan ts).EntireRow, Rows(1)).Copy Worksheets.Add ActiveSheet.Paste Application.CutCopyMode = False -- Jim Rech Excel MVP |
Txt Import problems.....
Thanks guys, I will go through that code and check it out.
I have also found that when I go to run the code on Office 97 (it was written in 2003) their are a few lines that need tweaking to work, most of which I have fixed but I am stuck with this; Error 91 Object variable with block variable not set Code breaks at line: Set sh = Sheets(1) Set rng = sh.[a1].CurrentRegion Tag = "PAGE:" num = Application.CountIf(rng, Tag) startrow = rng.Find(What:=Tag).Row MsgBox startrow.Row endrow = startrow + num Range(rng.Rows(startrow), rng.Rows(endrow - 1)).Delete <-----error occurs on this line Anyone know why? -Al "Jim Rech" wrote in message ... Instead of using Current Region to find the range with data you can use UsedRange, e.g.: ActiveSheet.UsedRange.Select If you still want to delete the empty rows a quick and easy way is to copy the rows with data to a new sheet like this: Union(ActiveSheet.UsedRange.SpecialCells(xlConstan ts).EntireRow, Rows(1)).Copy Worksheets.Add ActiveSheet.Paste Application.CutCopyMode = False -- Jim Rech Excel MVP |
Txt Import problems.....
The delete worked fine for me under Excel 97. This line did error though:
MsgBox startrow.Row That's because startrow is an integer not a range. It's a good idea to declare your variables and use Option Explicit so you know what each is. -- Jim Rech Excel MVP |
All times are GMT +1. The time now is 04:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com