usedrange giving wrong column number

Question

 Sub testing()


    'start searching for address
    Set wb = ThisWorkbook
    Set ws1 = wb.Sheets("Sheet1")
    Set ws2 = wb.Sheets("Sheet2")

    IncNum = Sheets("Sheet2").UsedRange.Columns.Count
    ExcNum = Sheets("Sheet1").UsedRange.Columns.Count
    InrNum = Sheets("Sheet2").UsedRange.Rows.Count
    Exrnum = Sheets("Sheet1").UsedRange.Rows.Count

    Set sheet1Table = Sheets("Sheet1").UsedRange
    Set sheet2Table = Sheets("Sheet2").UsedRange
    'skip header
    For InrCounter = 2 To InrNum
        For ExrCounter = 2 To Exrnum
            If sheet1Table.Cells(InrCounter, 1) = sheet2Table.Cells(ExrCounter, 1) And sheet1Table.Cells(InrCounter, 2) = sheet2Table.Cells(ExrCounter, 2) And sheet1Table.Cells(InrCounter, 3) = sheet2Table.Cells(ExrCounter, 3) Then
                If IncNum = ExcNum Then
                    Exit For
                Else
                    Dim LastCofRowCounter, lastCofthisR As Integer
                    lastCofthisR = sheet1Table.Cells(ExrCounter, Columns.Count).End(xlToLeft).Column
                    For LastCofRowCounter = lastCofthisR + 1 To IncNum
                        Sheets("Sheet1").Cells(ExrCounter, LastCofRowCounter) = Sheets("Sheet2").Cells(InrCounter, LastCofRowCounter)
                    Next LastCofRowCounter
                End If
            ElseIf ExrCounter = Exrnum Then

        'fid the last row of mastersheet
        lrowEx = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For counterCofLastR = 1 To IncNum
           Sheets("Sheet1").Cells(lrowEx + 1, counterCofLastR) = Sheets("Sheet2").UsedRange.Columns(1).Cells(InrCounter, counterCofLastR)
        Next counterCofLastR
        End If
      Next ExrCounter

Next InrCounter
End Sub


    the table looks like  
        h1 h2 h3 h4
        x  x  x  x

the line ExcNum = Sheets("Sheet1").UsedRange.Columns.Count is giving me 9 instead of 4 and I have no idea why in this case...i tried on another worksheet with the same table and it worked fine.

I tried to call

  Sub ResetUsedRng()
    Application.ActiveSheet.UsedRange
  End Sub

before executing all the code but this did not work. I also read the related posts you guys linked which includes this

    Private Sub Workbook_BeforeSave _
     (ByVal SaveAsUI As Boolean, Cancel As Boolean)
     For Each Sh In ThisWorkbook.Worksheets
      x = Sh.UsedRange.Rows.Count
      Next Sh
    End Sub

I called Call Workbook_BeforeSave(True, False), which did not work either. Ideas?

Have you tried resetting the used range [see here](http://stackoverflow.com/questions/7423022/excel-getting-the-actual-usedrange/7423252#7423252) — Reafidy, Jul 29 '13 at 00:26

score 2 · Answer 1 · edited May 23 '17 at 11:52

2

From my experience the only reliable one is to use

Lastcol =  Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Lastrow =  Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

to determine the last cell of a used range. All other methos involving UsedRange or SpecialCells(xlLastCell) or End(xlUp) may give you wrong results.

edited May 23 '17 at 11:52

Community

1
1

answered Dec 29 '13 at 11:29

nixda

2,654
12
49
82

score 0 · Accepted Answer · answered Jul 29 '13 at 00:17

0

Excel is notoriously bad about updating the .UsedRange property. If your sheet at some point had data or formatting applied in column I, then it's likely that the .UsedRange property was never updated.

Since .UsedRange is so handy to use in VBA, I usually workaround this by crafting a sub that checks for the last occupied column and row in the sheet and Deletes any extra rows and columns included in Sheet.UsedRange. This will force an update of the property.

answered Jul 29 '13 at 00:17

Excellll

5,609
4
38
55

thanks for the insight can you give me a few lines of sample to do this? I am completely new to excel vba.@Excellll – user2600411 Jul 29 '13 at 00:21
See the link in Reafidy's comment above. – Excellll Jul 29 '13 at 03:30

score -1 · Answer 3 · answered Dec 29 '13 at 08:31

This two line do the magic. It uses specialcells to find out last column and last row.

  usedCol = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
  usedRow = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

For more info visit Microsoft's site

http://msdn.microsoft.com/en-us/library/office/ff196157.aspx

usedrange giving wrong column number

3 Answers3