VBA: UsedRange Not Updating Correctly
I have a problem updating UsedRange every time I use it. I have this in my code:
Sheets("Campaign").UsedRange 'Refresh UsedRange
LastRow = Sheets("Campaign").UsedRange.Rows.Count
This is a check on the last line used on a particular worksheet. It now works and outputs the correct integer on first run, but does not update if you remove rows from the column with the most rows. The point is, it gets updated if you add more rows to 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 highlight an integer that usedRange produces.
source to share
Try combining the UsedRange command with this:
ActiveCell.SpecialCells(xlCellTypeLastCell).Row
The SpecialCells (xlCellTypeLastCell) code refers to the last used cell in the worksheet. In Excel, the equivalent command is {Ctrl End}.
In your code:
Sheets("Campaign").UsedRange 'Refresh UsedRange
LastRow = Sheets("Campaign").Range("A1").SpecialCells(xlCellTypeLastCell).Row
It should be noted that sometimes just deleting the data (Del key) is not enough. Try to actually delete the cells ({Ctrl -} or {Right click Delete}).
Another code that works, unless your worksheet is complete to the last line:
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 to make 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 incorrectly deleted cells in column A.
source to share