K
Kimberly Anne
I have a workbook that has 200 tabs with data on them. The data is
imported from outside text files that are tab delimited. The files
contain rows of data, then a blank row, then rows of diffent data with
only one value. They look something like this:
(part 1)
20 (tab) 0.1 (tab) 0.2 (tab) 0.3 (tab) 0.4 (tab) 0.5 (tab)
21 (tab) 0.2 (tab) 0.3 (tab) 0.4 (tab) 0.5 (tab) 0.6 (tab)
.......
70 (tab) 1.0 (tab) 2.0 (tab) 3.0 (tab) 4.0 (tab) 5.0 (tab)
(blank line, then part 2)
25 (tab) 0.6
26 (tab) 0.7
27 (tab) 0.8
Etc, etc, etc.
The range in part 1 is not always 20 to 70 - sometimes it's 0 to 80,
others its 18 to 90. There's no pattern to it.
Some of the files are wonky and in part 1 have a tab, then the first
field of data, and then another tab so that the data is beginning in
column B instead of column A. However - in part 2 they still start in
column A, even if this is happening. I wrote a macro to try and take
care of this - but it is killing my memory usage for some reason and I
get the dreaded "system does not have enough resources to complete this
task" error.
Here's the macro - can anyone see what I'm doing wrong that is causing
a ton of memory to be eaten? I can't just delete column A as there are
still values in column A in part 2.
Sub RemoveBlankFirstColumn()
Sheets("SheetList").Activate
For i = 1 To 200
' Calls the current sheet being looked at
Range("type_num") = i
Calculate
' Brings in the sheet name
curr_sheet = Range("type_sheet")
'Activates the sheet
Sheets(curr_sheet).Activate
'Checks for blanks in column A
If Range("a1").Value = Empty And Range("a10").Value = Empty Then
'Selects blank range and deletes it
Range("A1").Select
Selection.End(xlDown).Activate
ActiveCell.Offset(-1, 0).Activate
Range(Selection, Selection.End(xlUp)).Select
Selection.Delete Shift:=xlToLeft
End If
Next i
End Sub
Can anyone help?
Thanks much,
Kim
imported from outside text files that are tab delimited. The files
contain rows of data, then a blank row, then rows of diffent data with
only one value. They look something like this:
(part 1)
20 (tab) 0.1 (tab) 0.2 (tab) 0.3 (tab) 0.4 (tab) 0.5 (tab)
21 (tab) 0.2 (tab) 0.3 (tab) 0.4 (tab) 0.5 (tab) 0.6 (tab)
.......
70 (tab) 1.0 (tab) 2.0 (tab) 3.0 (tab) 4.0 (tab) 5.0 (tab)
(blank line, then part 2)
25 (tab) 0.6
26 (tab) 0.7
27 (tab) 0.8
Etc, etc, etc.
The range in part 1 is not always 20 to 70 - sometimes it's 0 to 80,
others its 18 to 90. There's no pattern to it.
Some of the files are wonky and in part 1 have a tab, then the first
field of data, and then another tab so that the data is beginning in
column B instead of column A. However - in part 2 they still start in
column A, even if this is happening. I wrote a macro to try and take
care of this - but it is killing my memory usage for some reason and I
get the dreaded "system does not have enough resources to complete this
task" error.
Here's the macro - can anyone see what I'm doing wrong that is causing
a ton of memory to be eaten? I can't just delete column A as there are
still values in column A in part 2.
Sub RemoveBlankFirstColumn()
Sheets("SheetList").Activate
For i = 1 To 200
' Calls the current sheet being looked at
Range("type_num") = i
Calculate
' Brings in the sheet name
curr_sheet = Range("type_sheet")
'Activates the sheet
Sheets(curr_sheet).Activate
'Checks for blanks in column A
If Range("a1").Value = Empty And Range("a10").Value = Empty Then
'Selects blank range and deletes it
Range("A1").Select
Selection.End(xlDown).Activate
ActiveCell.Offset(-1, 0).Activate
Range(Selection, Selection.End(xlUp)).Select
Selection.Delete Shift:=xlToLeft
End If
Next i
End Sub
Can anyone help?
Thanks much,
Kim