• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Assistance to combine 2 Macros

Bimmy

Member
Hi,

Below is a macro that deletes all rows that are blank -

Code:
Sub DelEmptyRows()
  Dim i As Long, iLimit As Long
    iLimit = ActiveSheet.UsedRange.Rows.Count
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual    'pre XL97 xlManual
  For i = iLimit To 1 Step -1
    If Application.CountA(Cells(i, 1).EntireRow) = 0 Then
       Cells(i, 1).EntireRow.Delete
    End If
  Next i
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
   iLimit = ActiveSheet.UsedRange.Rows.Count   'attempt to fix lastcell
   ActiveWorkbook.Save
End Sub
... and below is a Recorded macro that keeps First header and deletes remaining rows with headers in it -

Code:
Sub Macro3()
'
' Macro3 Macro
'

'
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$F$68").AutoFilter Field:=1, Criteria1:="Date"
    ActiveCell.Offset(20, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveCell.Rows("1:37").EntireRow.Select
    Selection.Delete Shift:=xlUp
    ActiveCell.Offset(-20, 0).Range("A1").Select
    Selection.AutoFilter
End Sub
Kindly assist in combining both the codes. Also if possible simplify the code.

Any help will be greatly appreciated.

Please find attached sheet with sample data.
 

Attachments

NARAYANK991

Excel Ninja
Hi ,

Your requirement is not clear to me.

With reference to the data which is present in Sheet2 , can you also post an output sheet which contains data after the second macro has operated on your input data in Sheet2 ?

Narayan
 

Marc L

Excel Ninja
Hi !​
Kindly assist in combining both the codes.
No as boh codes are not very good ! The first is the slowest way (the more rows, the slower),​
the second is the middle way but as a good code does not need to select anything …​
For big data the faster way is to follow a 'child' way when operating manually & needing only few seconds​
(so faster than your both codes !) via a VBA procedure reproducing it.​
As yet explained in this forum rules, the better is you well elaborate at least your need and an attachment​
with before (raw data) and after (expected result) worksheets like any forum expects in the initial post !​
 
Top