How to scroll through excel columns using vbscript

Hi guys,

Im reading in data from an excel sheet and therefore I need to scroll through each column of the sheet. Im trying to do this by creating a for loop with length = number of columns. To do this i need the ascii number for each column. My code works from A-Z, but when it gets to AA falls over. So far ive got -

CurCol = 65
For Col = CurCol+1 To CurCol +57
If (CurCol = 91) Then      
  If (ColPreFix = "" ) Then
     ColPreFix = 65
     Else
    ColPreFix = Asc(vColPreFix) +1
  End If
ColPreFix = Chr(ColPreFix)
CurCol = 65
End If
ColName = ColPreFix & Chr(CurCol)

Any know why this is falling over at this stage and how i can get the loop to continue to AA, AB.....BA,BB,BC, etc
DiCanio13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Curt LindstromCommented:
How about a different approach using the method used in this code snippet to step through the columns?

Curt
   Sub DeleteBlankColumns()
    Dim Col As Long, ColCnt As Long, Rng As Excel.Range
     
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
     
On Error GoTo Exits:
 
    'Determine range to process
    If Selection.Columns.Count > 1 Then
        'Rng = selected cells
        Set Rng = Selection
    Else
        'Rng = filled cells in column 1
        Set Rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column()))
    End If
    ColCnt = 0
    
    'Loop through all columns
    For Col = Rng.Columns.Count To 1 Step -1
        'If entire column is blank
        If Application.WorksheetFunction.CountA(Rng.Columns(Col).EntireColumn) = 0 Then
            'Delete Column
            Rng.Columns(Col).EntireColumn.Delete
            ColCnt = ColCnt + 1
        End If
    Next Col
     
Exits:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 'Clean up
 Set Rng = Nothing
End Sub

Open in new window

0
Curt LindstromCommented:
You may find the following functions useful.

Curt
'Using Functions to Get a Range
'By Brandtrock (www.vbaexpress.com)
'KB Entry: http://www.vbaexpress.com/kb/getarticle.php?kb_id=496
'Thanks to Brandtrock
 
Option Explicit
 
Dim MyRange As Excel.Range
 
Sub MoveIt()
 
    'Determine used range of active worksheet using 4 fuctions (Below)
    Set MyRange = Range((Cells(xlFirstRow, xlFirstCol)), (Cells(xlLastRow, xlLastCol)))
    MyRange.Copy Destination:=Sheets("Sheet2").Range("B4")
     'this range can be changed to whatever you like
     
    'Clean up
    Set MyRange = Nothing
End Sub
 
Function xlFirstCol(Optional WorksheetName As String) As Long
     
    'Check for optional worksheetname else use activesheet
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    
     '  find the first populated column in a worksheet
    With Worksheets(WorksheetName)
        xlFirstCol = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByColumns, xlNext).Column
    End With
     
End Function
 
Function xlFirstRow(Optional WorksheetName As String) As Long
     
    'Check for optional worksheetname else use activesheet
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    
     '  find the first populated row in a worksheet
    With Worksheets(WorksheetName)
        xlFirstRow = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlNext).Row
    End With
     
End Function
 
Function xlLastRow(Optional WorksheetName As String) As Long
     
    'Check for optional worksheetname else use activesheet
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    
     '    find the last populated row in a worksheet
    With Worksheets(WorksheetName)
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlPrevious).Row
    End With
     
End Function
 
Function xlLastCol(Optional WorksheetName As String) As Long
     
    'Check for optional worksheetname else use activesheet
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    
     '    find the last populated column in a worksheet
    With Worksheets(WorksheetName)
        xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByColumns, xlPrevious).Column
    End With
End Function

Open in new window

0
DiCanio13Author Commented:
Thanks epaclm,
but i need to return the ascii column number and letter, ie '65' and 'A'  so that i can use these further along in my solution
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Curt LindstromCommented:
Try this macro.

Curt
Sub Column_Letter()
Dim CurCol As Long
Dim ColPreFix As Long
Dim Colname As String
Dim ColCount As Long
 
For CurCol = 65 To 256
If CurCol = 91 Then
  If ColPreFix = 0 Then
     ColPreFix = 65
     Else
    ColPreFix = ColPreFix + 1
  End If
  ColCount = ColCount + 26
End If
If ColPreFix = 0 Then
Colname = Chr(CurCol)
Else
Colname = Chr(ColPreFix) & Chr(CurCol - ColCount)
End If
Next CurCol
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DiCanio13Author Commented:
Hey Curt,

Thanks for that....its almost working, it correctly scrolls through A-Z and AA-AZ, but when it gets to BA it falls over.
Currently we've got -

If (vCurCol = 91) Then
If (vColPreFix = "") Then
vColPreFix = 65
Else
vColPreFix = vColPreFix + 1
End If
vColCount = vColCount + 26
End If
If (vColPreFix = 0) Then
vColname = Chr(vCurCol)
Else
vColname = Chr(vColPreFix) & Chr(vCurCol - vColCount)
End If

do we need to set a condition for when the ascii char gets to 117 (BA) or something like that?
0
DiCanio13Author Commented:
Cheers Curt, ive got it now -

 vCurCol = 65
 For vCol = vCurCol+1 To vCurCol +57

If (vCurCol = 91) Then
  If (vColPreFix = 0) Then '0
     vColPreFix = 65
     Else
     vColPreFix = vColPreFix + 1
       End If
     vColCount = vColCount + 26
End If

If (vCurCol = 117) Then
  If (vColPreFix = 0) Then      '0
     vColPreFix = 65
     Else
     vColPreFix = vColPreFix + 1
 End If
    vColCount = vColCount + 26
End If

If (vColPreFix = 0) Then
vColname = Chr(vCurCol)
Else
vColname = Chr(vColPreFix) & Chr(vCurCol - vColCount)
End If
0
Curt LindstromCommented:
That's good! If you have any problem with that one you can try this version

Cheers,
Curt
Sub Column_Letter()
    Dim CurCol As Long
    Dim ColPreFix As Long
    Dim Colname As String
    Dim ColCount As Long
 
    For CurCol = 65 To 256
        Select Case CurCol
        Case 91
            ColPreFix = 65
            ColCount = 65
        Case 117, 143, 169, 195, 221, 247
            ColPreFix = ColPreFix + 1
            ColCount = 65
        Case Else
            If CurCol > 91 Then
                ColCount = ColCount + 1
            End If
        End Select
        If ColPreFix = 0 Then
            Colname = Chr(CurCol)
        Else
            Colname = Chr(ColPreFix) & Chr(ColCount)
        End If
    Next CurCol
End Sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.