VBA - Select cells right that have formatting

[JimDandy]

The following line of code is in a Sub that copies a formula across to the right. It works fine when there's already something in those cells but if I use this selection and there's nothing in the cells to the right it'll copy across every possible cell in the worksheet. To prevent this I enter a character in the last column I'm working on but I'm wondering if there's an option for the select statement that will select to the right up to the last cell that has some sort of formatting instead of data.

This way I can draw borders around the area I'm working on and select/copy right up to the last cell with formatting instead of accidentally entering data in every last cell to the right on the worksheet filling it with thousands of data points and formulas unnecessarily. I use this particular macro a lot in my work and it can stop my computer for a long periods of time if I do this with a whole column of formulas to copy across.

Bottom line, what I'd like to do is select to the right only cells that have some form of formatting.

This is the line used now.

Please Login or Register  to view this content.

[MarvinP]

Hi JimDandy,

I think you are looking for some function dealing with "UsedRange"

Try to incorporate Application.ActiveSheet.Usedrange.Column into a formula. UsedRange will give the address of the last row and last column used on a sheet.

[JimDandy]

Thanks, I can see some success with that but I'll need to do some more trial and error to get it right.