Hello Folks,
So I have a code that works for all but one situation. This code names the row in a region of data. (JosephP was nice enough to guide me on how to do this of a whole region, but I still have to be able to do it for a whole row.
Sub horizontalnaming()
Dim horng As Range
ActiveCell.Select
Selection.End(xlToLeft).Select
Set horng = Range(Selection, Selection.End(xlToRight))
With horng
.Resize(, .Columns.Count - 1).Offset(, 1).Name = "'" & .Worksheet.Name & "'!" & .Cells(1).Value
End With
End If
End Sub
My solution is to say: if you are at the most left column, then skip the left shift. I do this because if you implement the End(xlToLeft) when you are at the left most point of a region you go somewhere else. No good.
Sub horizantalselect()
Dim horng As Range
ActiveCell.CurrentRegion.Select
If ActiveRange.Columns.Count = (CurrentRegion.Columns.Count - (CurrentRegion.Columns.Count - 1)) Then
Set horng = Range(Selection, Selection.End(xlToRight))
horng.Resize(, horng.Columns.Count - 1).Offset(, 1).Name = "'" & ActiveSheet.Name & "'!" & horng.Cells(1).Value
Else
Selection.End(xlToLeft).Select
Set horng = Range(Selection, Selection.End(xlToRight))
horng.Resize(, horng.Columns.Count - 1).Offset(, 1).Name = "'" & ActiveSheet.Name & "'!" & horng.Cells(1).Value
End If
End Sub
I though that this numeric statement would work but it doesn't. It says an object is required and I'm uncertain why.
If someone could help with this, I would be immensely grateful. Something tells me I'm doing something silly, but who knows. Thank you in advance.
Bookmarks