Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to scroll through excel columns using vbscript

Posted on 2008-11-04
7
Medium Priority
?
963 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:DiCanio13
  • 4
  • 3
7 Comments
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 22882755
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
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 22882768
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
 

Author Comment

by:DiCanio13
ID: 22882803
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Accepted Solution

by:
Curt Lindstrom earned 500 total points
ID: 22882955
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
 

Author Comment

by:DiCanio13
ID: 22883169
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
 

Author Comment

by:DiCanio13
ID: 22883359
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
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 22883406
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question