+ Reply to Thread
Results 1 to 3 of 3

finding the end of rows and columns and taking union of sets

  1. #1
    Registered User
    Join Date
    06-11-2013
    Location
    Tampa
    MS-Off Ver
    Excel 2010
    Posts
    19

    finding the end of rows and columns and taking union of sets

    Hi,
    I have a code in software that exports results to excel. But it stores the numbers associated with indices as text. I need to change all of them to numbers.
    So I have written the following code, that search through the folder, converts text to numbers and colses the file.

    Sub ConvertText2NumberFiles()
    Dim v As Variant
    Dim rng1 As Range, bk As Workbook
    Dim i As Long
    ChDrive "C"
    ChDir "C:\ ...\analysis"

    v = Application.GetOpenFilename( _
    FileFilter:="Excel Files (*.xlsx),*.xlsx", _
    Title:="Select Multiple Files", _
    MultiSelect:=True)
    If IsArray(v) Then
    For i = LBound(v) To UBound(v)
    Set bk = Workbooks.Open(v(i))

    bk.ActiveSheet.Range("a1:t999").Select
    For Each xCell In Selection
    If xCell.Value = "Undef" Then xCell.Value = 0
    xCell.Value = CDec(xCell.Value)
    If xCell.Value = 0 Then xCell.Value = Null
    Next xCell
    bk.Close SaveChanges:=True
    Next
    Else
    MsgBox "No files to process"
    End If

    End Sub

    The problem is it takes time becuase it is going through A LOT OF cells. I only need at most the first 4 columns and the first 2 rows.
    So I wanted to use the union. I wrote the following:

    Set IndexColumns = bk.ActiveSheet.Range(Columns("A") & ":" & Columns("A").SpecialCells(xlLastCell))
    Set IndexRows = bk.ActiveSheet.Range(Rows("1") & ":" & Rows("1").SpecialCells(xlLastCell))
    Application.Union(Range("IndexColumns"), Range("IndexRows")).Select

    Which doesn't work! Morover it is only for one column and one row.
    NOTE1 : Like any matrix, the intersection of index columns and index rows is blank!
    NOTE2 : The lenght of indices is different from one file to another
    can anybody help me with this problem?
    Thanks
    Last edited by AnnaDana; 06-13-2013 at 04:14 PM.

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: finding the end of rows and columns and taking union of sets

    I only need at most the first 4 columns and the first 2 rows.
    Have you tried replacing
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.
    Alf

  3. #3
    Registered User
    Join Date
    06-11-2013
    Location
    Tampa
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: finding the end of rows and columns and taking union of sets

    Hey Alf,
    I didn't mean the intersection of the first 4 columns and the first 2 rows.
    What I mean is I want the code to go through the first 4 columns from the first cell it finds to the last cell, and then do the same for the rows 1 and 2.
    Then I can take their union and work with that.
    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1