SelectionChange

[Robert1688]

I've made the following to autofit the row of the cell I selected, but I want it to be the origin row height after another cell is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.EntireRow.AutoFit
End Sub

 

[yogia]

Hi Robert:

Is Origin a fixed range, such as cell C3? ... if so then let us try ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set Origin = Range("C3")
    Origin.EntireRow.AutoFit
End Sub

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[Robert1688]

Sorry, I did not describe it clearly.
Like this:
I select C3, the 3rd row becames autofit, I can read the whole data in this row.
And then, I select D4, then the 3rd row will be back to its origin height, like 12.75, and the 4th row becames autofit.....

 

[handleman]

You mean like:

Dim OldHt As Double
Dim NewHt As Double
Dim OldSel As Range


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (Target.Row <> OldSel.Row) And (Target.Rows.Count = 1) Then
    
    If Not OldSel Is Nothing Then
        OldSel.RowHeight = OldHt
    End If

    Set OldSel = Target
    OldHt = Target.EntireRow.Height
    
    Target.EntireRow.AutoFit
    
End If
End Sub

 

[Robert1688]

Thanks, handleman

As I run your codes, it says "Object variable or With block variable not set", it may points to OldSel.

I've tryed this kind of method before, set Oldsel as Target at end of program.

 

[Robert1688]

I've got it solved:

Public ff, tt
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Rows(ff).RowHeight = tt
ff = Target.Row
tt = Target.RowHeight
Target.EntireRow.AutoFit
End Sub