merge 2 codes into 1

[Trevor3007]

good evening,

Hi I use this code:-

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.UsedRange.Borders.Weight = xlThick
    Dim FirstRow As Long, LastRow As Long, i As Long
    Application.EnableEvents = False
    With ActiveSheet.UsedRange
        FirstRow = .Row
        LastRow = .SpecialCells(11).Row
    End With
    For i = LastRow To FirstRow Step -1
        If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).Delete
    Next
    Application.EnableEvents = True
   
     
End Sub

How do I include the code below into the above , so they both run as one?

Sub WrapandFit()
 
    ActiveCell.WrapText = True
    ActiveCell.EntireRow.AutoFit
 
End Sub

MTIA
Trevor3007

 

[PCL]

Without testing

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.UsedRange.Borders.Weight = xlThick
    Dim FirstRow As Long, LastRow As Long, i As Long
    Application.EnableEvents = False
    With ActiveSheet.UsedRange
        FirstRow = .Row
        LastRow = .SpecialCells(11).Row
    End With
    For i = LastRow To FirstRow Step -1
        If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).Delete
    Next

    ActiveCell.WrapText = True
    ActiveCell.EntireRow.AutoFit 
    Application.EnableEvents = True  
End Sub

 

[MARK858]

Maybe...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim FirstRow As Long, LastRow As Long, i As Long
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    
    Application.EnableEvents = False

    ActiveSheet.UsedRange.Borders.Weight = xlThick
    Target.WrapText = True
    Target.EntireRow.AutoFit

    With ActiveSheet.UsedRange
        FirstRow = .Row
        LastRow = .SpecialCells(11).Row
    End With

    For i = LastRow To FirstRow Step -1
        If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).Delete
    Next

    Application.EnableEvents = True
End Sub

 

[Trevor3007]

Maybe...

hello mark

Many thanks for your help again. With your help & a bit of 'trail & error' I produced this:-

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim FirstRow As Long, LastRow As Long, i As Long
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    
    Application.EnableEvents = False

    ActiveSheet.UsedRange.Borders.Weight = xlThick
    Target.WrapText = True
    Target.EntireRow.AutoFit

    With ActiveSheet.UsedRange
        FirstRow = .Row
        LastRow = .SpecialCells(11).Row
    End With

    For i = LastRow To FirstRow Step -1
        If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).Delete
    Next

    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.UsedRange.Borders.Weight = xlThick
    Dim FirstRow As Long, LastRow As Long, i As Long
    Application.EnableEvents = False
    With ActiveSheet.UsedRange
        FirstRow = .Row
        LastRow = .SpecialCells(11).Row
    End With
    For i = LastRow To FirstRow Step -1
        If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).Delete
    Next
    Application.EnableEvents = True




If MsgBox("Do you want to saveworksheet?", vbYesNo) = vbYes Then


ActiveWorkbook.SaveAs ("C:\test\testdata_As_At_ " & Format(Now(), "DD-MMM-YYYY hh_mm_ss") & ".xls")


Else


End If


End Sub

I just got to find out how to email it as an attachment using a predefined *oft file...

KR
Trevor

 

[Trevor3007]

Without testing

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.UsedRange.Borders.Weight = xlThick
    Dim FirstRow As Long, LastRow As Long, i As Long
    Application.EnableEvents = False
    With ActiveSheet.UsedRange
        FirstRow = .Row
        LastRow = .SpecialCells(11).Row
    End With
    For i = LastRow To FirstRow Step -1
        If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).Delete
    Next

    ActiveCell.WrapText = TruePCL

    ActiveCell.EntireRow.AutoFit 
    Application.EnableEvents = True  
End Sub

Hello PCL

all works fine , just need to sort how to email it as an attachment using a predefined *.oft template?

KR
Trevor

 

[PCL]

just need to sort how to email it as an attachment using a predefined *.oft template?

I would suggest to open a new thread it is another topic
After we can merge all codes ...!

 

[Trevor3007]

I would suggest to open a new thread it is another topic
After we can merge all codes ...!

many thanks...

oops ....yes i noticed that :}

all the very best