CODEWITHSUNDEEP
vbaexcel
ayasocool
ayasocool

Reputation: 157

VBA: UsedRange Does not update correctly

I'm having a problem updating UsedRange every time I'm using it. I have this in my code:

Sheets("Campaign").UsedRange 'Refresh UsedRange
LastRow = Sheets("Campaign").UsedRange.Rows.Count

This is to check the the last row used in a particular work sheet. Now, it works and outputs the correct integer when you run it for the first time, but does not update if you remove rows from the column that has the highest amount of rows. The thing is, it DOES update if you add more rows into that particular column.

Here is my actual code:

Sub CMPGN_MCRO()

    Sheets("Campaign").UsedRange 'Refresh UsedRange.
    LastRow = Sheets("Campaign").UsedRange.Rows.Count

    Dim RangeString As String
    Worksheets("Campaign").Select
    Range("A1:A" & LastRow).Select
    RangeString = Selection.Address

    Range("A1").End(xlToRight).Select
    RangeString = RangeString & ":" & Selection.Address 'Selects all the data in Campaign Worksheet.

    Dim C As Range
    i = 2
    For Each C In Worksheets("Campaign").Range(RangeString).Cells
      If Mid(C.Value, 6, 2) = "AC" Then
      Worksheets("Sheet3").Range("A" & i) = C.Value 'Echo out all the filtered data and output it to another worksheet.
      i = i + 1
    End If
    Next
MsgBox (LastRow)
End Sub

PS: I used MsgBox to echo out the integer that the usedRange produces.

Upvotes: 5

Views: 10651

Answers (1)

vacip
vacip

Reputation: 5426

Try combining the UsedRange command with this:

ActiveCell.SpecialCells(xlCellTypeLastCell).Row

The code SpecialCells(xlCellTypeLastCell) refers to the last used cell on the worksheet. In Excel, the similar command is {Ctrl End}.

In your code:

Sheets("Campaign").UsedRange 'Refresh UsedRange
LastRow = Sheets("Campaign").Range("A1").SpecialCells(xlCellTypeLastCell).Row

A thing to note is that sometimes just deleting the data (Del key) is not enough. Try actually deleting the cells ({Ctrl -} or {Right click Delete}).

Another code that works, unless your worksheet is filled to the last row:

LastRow = cells(Sheets("Campaign").Range("A1").SpecialCells(xlCellTypeLastCell).Row,1).offset(1,0).end(xlUp).row

What it does: Find the last cell from cell A1, then down one to be sure we are in an empty cell, then use ctrl-up to find the last cell filled with data. A bit messy, but works, even if there are some not properly deleted cells in column A.

Upvotes: 4

Related Questions