all community dating site - Excel usedrange not updating

Nothing looked unusual – other sample files had similar data and reports, but were a much smaller file size. As you probably know, some sheets look like they only have a bit of data on them.

However, if you press Ctrl End, to go to the last used cell, you end up way out in the wilderness.

The Worksheet object contains a Cells property that is a Range object that comprises a reference to all the cells in the Worksheet.

excel usedrange not updating-55

online datings sites for rich gays - Excel usedrange not updating

You’ll need to get the Range object for the cells which formula you wish to set and then set its Formula property: Setting cell number formats, font, foreground and background colors are done in a similar fashion as setting cell values.

You first need to get a reference to the Excel Range object which formatting you want to change.

Most of the sample files are just a few kilobytes in size, but this one was about 1.5 MB. Here's how I found the problem, and a macro that you can use for troubleshooting in your workbooks. Cells(l Count, l Fields), _ Address:="", _ Sub Address:="'" & ws. " & str LC, _ Screen Tip:=str LC, _ Text To Display:=str LC l Count = l Count 1 End With End If Next ws With ws Temp .

An Excel file that's 1.5 MB might not seem too alarming, but I like to keep the sample files small, so people can download them quickly. First, I took a quick look at the sheets in the workbook, to see if there was an obvious problem.

After that, the file size went down to about 40 KB.

You can see the much smaller used range in the list below. I really didn't want to test each sheet individually, with the Ctrl End shortcut. To save time, I used the Sheet Info tool in my Excel Tools add-in, and it showed that one of the sheets had a used range that ended in the last row! To fix it, I manually deleted all the unused rows, and saved the file. There are instructions on my website, along with sample code for programmatically resetting the used range. You could land in the last column, or down thousands of rows, far away from any of the data on the worksheet. Maybe there used to be data there, or something was accidentally formatted, long ago. Cells(l Count, 1), _ Address:="", _ Sub Address:="'" & ws. You might also notice a Value2 property, which is an interesting one and reading more about this property on MSDN yielded the following piece of information, which is good to know: “The only difference between this property and the Value property is that the Value2 property doesn’t use the Currency and Date data types.

Tags: , ,