Refreshing the UsedRange object

[Sathyaish]

How does one refresh the UsedRange object? For instance, I type a value in the cell A1 and then type another value in the cell G55. The Used Range then becomes A1:G55. If I now erase the contents of the cell G55 and type some content in the cell D12. Although the area being used by the spreadsheet now is only A112, the UsedRange object still contains the area A1:G55. How do I flush the contents of the UsedRange object to reflect its new range?

[GMWhite]

Hi,

What you are trying to do should work. There should be no need to reset the usedrange under normal circumstances. However, I have just discovered that, under certain circumstances, the usedrange can get out of synch with the sheet.

To solve your problem all you need to do is to write a piece of code to select the usedrange i.e.
private sub main()
sheet1.select
sheet1.usedrange.select
end sub

Then look at the sheet and delete the rows that are part of this incorrect usedrange. Note, don't just delete the values or clear the cells but delete the entire row. Now any change to the usedrange should be detectable using VBA.

The next thing you need to do is to understand how you managed to put the sheet into this state. That is where I start to run out of answers.

In my case I had a sheet with a data dump from some another system. This system exports directly to an .xls. The result is a page with a messed up idea of usedrange - it's like it never forgets or never updates. Also if you grab the contents of this usedrange and put them on another sheet you encounter the same problem. I tried this everyway I know and whatever it is is carried over even with paste values only.

What is really interesting is that I wrote some code to simply capture the usedrange, assign it to an array and write the array to another sheet. Simple stuff... It worked fine on my version of Excel (2002). Here is the code:

******************************************************
Option Base 1
Option Explicit
Public Sub main()
' This controls the whole program
Dim my_array() As Variant, problem As Boolean, format_range As Range, get_range As Range


get_data my_array, get_range, format_range, problem ' This works out what data to grab, grabs it and shoves it in the array
If problem = False Then
munge_data my_array ' This one operates on the array, up to you how to use this one
print_data my_array, get_range, format_range ' This one prints out the array on Sheet3
Else
MsgBox "Either sheet1 was empty or it just had one value"
End If

End Sub
Private Sub get_data(any_array() As Variant, get_range As Range, format_range As Range, problem As Boolean)
'This is really simple. It makes a range and calls another routine to work out exactly what the _
used range is on Sheet1 e.g. D3 to G9. Then it redimensions an array to be the same size and writes the value _
of the range to the array.

findusedrange get_range, format_range, problem
ReDim any_array(Sheet1.UsedRange.Rows.Count, Sheet1.UsedRange.Columns.Count)
If problem = False Then
get_range.Copy
format_range.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
get_range.NumberFormat = "General"
any_array = get_range.Value
End If

End Sub
Private Sub findusedrange(get_range As Range, format_range As Range, problem As Boolean)
Dim start_row As Single, end_row As Single, start_col As Integer, end_col As Integer

problem = False

start_row = Sheet1.UsedRange.Row
end_row = Sheet1.UsedRange.Row + Sheet1.UsedRange.Rows.Count - 1
start_col = Sheet1.UsedRange.Column
end_col = Sheet1.UsedRange.Column + Sheet1.UsedRange.Columns.Count - 1

If Application.WorksheetFunction.CountA(Sheet1.Cells) <= 1 Then problem = True

Set get_range = Sheet1.Range(Sheet1.Cells(start_row, start_col), Sheet1.Cells(end_row, end_col))
Set format_range = Sheet8.Range(Sheet8.Cells(start_row, start_col), Sheet8.Cells(end_row, end_col))

End Sub
Private Sub munge_data(any_array() As Variant)
' Up to you what you make this one do!

End Sub
Private Sub print_data(any_array() As Variant, get_range, format_range As Range)
'This just prints out any 2D array to Sheet3
Dim print_range As Range
Set print_range = Sheet5.Range(Sheet5.Cells(1, 1), Sheet5.Cells(UBound(any_array, 1), UBound(any_array, 2)))
Sheet5.Cells.Clear
print_range.Value = any_array

format_range.Copy
get_range.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
print_range.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

******************************************************
However, when I sent this code to a friend who was running a much newer version of Excel it crashed on the print_range.value = any_array. I get a run-time error '1004': Application-defined or object-defined error. {note that this code works for them with other data, just not this suspect data dump!}

I can make the code work on their machine by grunting through the elements in the array and assigning them individually to cells in the print sheet. The worrying thing is that when you start to look at this sheet using VBA you see the same usedrange confusion. So I suspect that the usedrange confusion and the Excel fail are related.

It also turns out that this data messes up pivot tables too. But I can't see what the problem with it is. It must be some property that I can't see; which the usedrange object is aware of.

Can anyone help me?

[GMWhite]

I think I have found out what caused this in my case...

Been talking to our IT chap and he seems to think that the database is doing the following…



Excel sees cells as single columns and rows however within the carbo database one cell can be used for multiple data (row or column) entries (ie if one project is operating in two countries they can create two records in one row of cells). So, this doesn’t look like a problem that is likely to be going away anytime soon. If anything it is going to get worse as the number of projects increase.

Looking at the sheets with the confused UsedRange the only solution is to manually delete every row (as explained above). I have asked my friend to try getting the export as a .csv - I'll let you know if that works.

[GMWhite]

Yes - the .csv works. So that is the answer.