asked on

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

Curt Lindstrom

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

Curt Lindstrom

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

DiCanio13

ASKER

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

ASKER CERTIFIED SOLUTION

Curt Lindstrom

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DiCanio13

ASKER

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?

DiCanio13

ASKER

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

Curt Lindstrom

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