Do only if Cell not empty on range

[FallingDown]

Hi all

I am running this code:

With Range("O:O")
With Target
If .MergeCells Then Call Notmerged Else .EntireRow.AutoFit
If Not .MergeCells Then .RowHeight = .RowHeight + 5
End With
End With

It's running fine, except I would not like it to run if the value in col. O is empty
I should incorporate this in this line

If Not .MergeCells Then .RowHeight = .RowHeight + 5

Any ideas?

Thx
FD

[Richard Buttrey]

Untested but maybe

With Range("O:O")
   With Target
      If .MergeCells Then Call Notmerged Else .EntireRow.AutoFit
      If Target <> "" Then
         If Not .MergeCells Then .RowHeight = .RowHeight + 5
      End If
   End With
End With

And just an observation. It seems to be generally acknowledged that merging cells is not really good practice since they can cause real problems with other Excel functionality and that you should avoid this unless absolutely necessary. There are usually other work arounds, and often a standard Format Alignment 'Center Across Selection' choice will do for centering across a range.

[FallingDown]

Thanks Richard

How strange, I tried that and it did not work...
Anyway... seems to be OK now

Many thanks

Yeah, I know about the merging issues, I do try to avoid it ;-)
My collegue already told me of about this :p

[FallingDown]

Sorry Richard

I just tested again, and now know why it did not work for me

If you copy/paste anything somewhere else with more then two cells, then this part you've added ens in runtime error 13 - Type missmatch

[Richard Buttrey]

Ah OK. Didn't realise more than 1 cell might be involved.

For situations like this I usually build in another test such as
IF Target.Cells.Count >1

to trap if someone tries to copy more than one cell.

[FallingDown]

Richard

Thanks again, but not sure this helps.
Maybe I was not clear enough
The part the user copy/pastes is from another report

Then the user pastes those data into this worksheet (ranges A:N)
But when the users pastes it ends in the runtime

[FallingDown]

Hi Richard

OK, so now I really have it

With Range("O:O")
With Target
If Target.Cells.Count > 1 Then
Application.DisplayStatusBar = True
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
Else
End If
If .Cells = "" Then
Application.DisplayStatusBar = True
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
Else
End If
If .MergeCells Then Call Notmerged Else .EntireRow.AutoFit
If Not .MergeCells Then .RowHeight = .RowHeight + 5
End With
End With

Now my last question is: I am sure this could be improved somehow, but since I am still quite new to VBA I was wondering whether you could take a look at the code?

[Richard Buttrey]

Hi,

Can you attach the workbook and also the other workbook from which the user copies cells.

[FallingDown]

Hi Richard

Sorry for nor replying earlier, was a bit busy doing other things

Anyway, I managed to do what I wanted

With Range("O:O")
Dim NewRwHt As Single
Dim c As Range
Dim cc As Range
Dim r As Integer
Dim ma As Range
With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
Set ma = c.MergeArea
r = c.MergeArea.Rows.Count
For Each cc In ma.Cells
Next
Application.ScreenUpdating = False
On Error GoTo 0
ma.MergeCells = False
c.EntireRow.AutoFit
NewRwHt = c.RowHeight + 10
ma.MergeCells = True
If NewRwHt < 12.75 Then NewRwHt = 12.75 Else NewRwHt = NewRwHt / r
If NewRwHt < 12.75 Then NewRwHt = 12.75
ma.RowHeight = NewRwHt
End If
End With
End With

Many thanks again for the help