incrementing alphabetically
Discussion
Using VBA in excel again!!
If I have a input file with 5 columns A-E
I have displayed them all on the screen but I only need A, C and E.
How can I erase them before the user gets to them??
So I start at A, look see if its on the list, if it is leave, if not the erase the whole column and increment to B and so on?
Cheers for any help.
If I have a input file with 5 columns A-E
I have displayed them all on the screen but I only need A, C and E.
How can I erase them before the user gets to them??
So I start at A, look see if its on the list, if it is leave, if not the erase the whole column and increment to B and so on?
Cheers for any help.
I don't understand what you're trying to ask.
If you want to delete a column, select an entire column and then call the range's Delete method on the Column object. E.g.:
Sheet1.Columns(1).Delete
As general points, if you don't need a column it is quicker not to import it in the first place if all you're going to have to do is delete it. However, if this is about slicing and dicing data, are you throwing data away that you might need later? If so, it may be more efficient to hide columns rather than delete them .... select a range that represents an entire column and then set its Hidden property to true, e.g. Sheet1.Columns(1).Hidden = True
If you want to delete a column, select an entire column and then call the range's Delete method on the Column object. E.g.:
Sheet1.Columns(1).Delete
Edited by ATG on Sunday 10th September 17:09
As general points, if you don't need a column it is quicker not to import it in the first place if all you're going to have to do is delete it. However, if this is about slicing and dicing data, are you throwing data away that you might need later? If so, it may be more efficient to hide columns rather than delete them .... select a range that represents an entire column and then set its Hidden property to true, e.g. Sheet1.Columns(1).Hidden = True
Edited by ATG on Sunday 10th September 17:18
ATG said:
I don't understand what you're trying to ask.
If you want to delete a column, call the Delete method on the Column object. E.g.:
Sheet1.Columns(1).Delete
If you want to delete a column, call the Delete method on the Column object. E.g.:
Sheet1.Columns(1).Delete
Edited by ATG on Sunday 10th September 17:09
I understand how to do it..... i just want to write this into a loop and use an external ini file which contains:
[Shine_Prem-Mgrs.txt]
Tablename=Premier RM (Retail)
ColumnsUsed=A,C,E
LastColumn=E
ColumnNumbers=3
Column1=Ranking
Column2=Name (Staff No)
Column3=Income Growth over 2005
HTMLName=Shine_Prem-Mgrs
so i want to write a loop that retains A,C and E and removes B and D off the table.
this coming from a pipe delimited file that looks like
1|"05029619"|"Wheeler SA Mrs (05029619)"|"Chippenham"|+9999.9
2|"01282999"|"Warrington KR Mr (01282999)"|"431 Oxford Street"|+0435.2
does that explain the situation
ATG said:
Yes, that does explain it. How are you importing the pipe-delimited file? Are you using the OpenText method? If so, you can avoid importing the columns you don't need, and it'll make the loop a little less hideous.
Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String
Dim TextQual As String
FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt), *.txt, All Files(*.*), *.*, Text Files(*.xls), *.xls"
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If
'Strip the FName here to the section that I want
' Sep = InputBox("Enter a single delimiter character.", _
' "Import Text File"
Sep = "|"
TextQual = Chr$(34)
ImportFile CStr(FName), Sep, TextQual
End Sub
from there it goes to the importFile method which is already hideous due to there being so many permutations of files that are imported into it and the users wanting different things to happen to individual files of which there are 25 different ones...
so a hideous loop aint going to make much difference...
Can you help with the hideous loop?
OK ... give us a sec
Let's assume sCols = "A,C,E" from reading the ColumnsUsed field in the ini file
Dim sArr() As String
sArr = Split(sCols, "," )
For i = Len(sArr) To 0 Step -1
Sheet1.Range(sArr(i) & "1" ).EntireColumn.Delete
Next i
This would do you ... BUT it assumes that sCols lists the columns left to right. If it doesn't do this then the whole thing will screw up because, for example, telling excel to delete column "B" then delete column "C" is not the same as saying delete column "C" then delete column "B".
If you want to guarantee this problem won't arise, then you could hide the columns rather thnan delete them, or you'd have to make sure the elements of sArr were sorted before you start deleting.
Let's assume sCols = "A,C,E" from reading the ColumnsUsed field in the ini file
Dim sArr() As String
sArr = Split(sCols, "," )
For i = Len(sArr) To 0 Step -1
Sheet1.Range(sArr(i) & "1" ).EntireColumn.Delete
Next i
This would do you ... BUT it assumes that sCols lists the columns left to right. If it doesn't do this then the whole thing will screw up because, for example, telling excel to delete column "B" then delete column "C" is not the same as saying delete column "C" then delete column "B".
If you want to guarantee this problem won't arise, then you could hide the columns rather thnan delete them, or you'd have to make sure the elements of sArr were sorted before you start deleting.
Edited by ATG on Sunday 10th September 17:51
Edited by ATG on Sunday 10th September 17:51
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff