Deletion using macro

[kiboodez]

I have a macro that grabs data from numerous sheets, does a couple of other small things, and then sorts by column A... If the entry for a row in column A is empty (at bottom of my sort list) but does have data in some other columns it is of little use to me. How do I add to the macro to delete the unwanted rows, bearing in mind I may have 60 useful rows one day, but only 35 the next? The solution would have to take into account that I will not always have the same number of rows to delete!

I would really appreciate some advice with this.

Thanks in advance.

[Marcol]

Try this

Sub DeleteExtraRows()
    Dim LastRowColA As Long, LastRowOnSheet As Long
    
    LastRowColA = Range("A" & Rows.Count).End(xlUp).Row
    LastRowOnSheet = ActiveSheet.UsedRange.Rows.Count
    Rows(LastRowColA + 1 & ":" & LastRowOnSheet).Delete
End Sub

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[kiboodez]

Thanks for that,
Alas, I had no success though I'm afraid (Not up to speed with VBA), I pasted iot in as follows...

Sub SortInt1()
'
' SortInt1 Macro
' Macro recorded 16/06/2010 by stsnedan02
'

'
Rows("2:559").Select
Selection.ClearContents
Range("A1").Select
Sheets("4C4MA1").Visible = True
Sheets("4D3MA1").Visible = True
Sheets("4E3MA1").Visible = True
Sheets("4D3MA1").Select
ActiveWindow.SmallScroll Down:=-45
Range("A1").Select
Sheets("4E3MA1").Select
Range("A1").Select
Sheets("4C4MA1").Select
Rows("2:36").Select
Selection.Copy
Sheets("Int 1 results").Select
Rows("2:2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
Sheets("4D3MA1").Select
Rows("2:36").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Int 1 results").Select
Rows("37:37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=36
Sheets("4E3MA1").Select
Rows("2:36").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Int 1 results").Select
Rows("72:72").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-84
Columns("A:AG").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter
Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A1").Select
Sheets(Array("4C4MA1", "4D3MA1", "4E3MA1")).Select
Sheets("4C4MA1").Activate
ActiveWindow.SelectedSheets.Visible = False
Range("A1").Select
End Sub
Sub DeleteExtraRows()
Dim LastRowColA As Long, LastRowOnSheet As Long

LastRowColA = Range("A" & Rows.Count).End(xlUp).Row
LastRowOnSheet = ActiveSheet.UsedRange.Rows.Count
Rows(LastRowColA + 1 & ":" & LastRowOnSheet).Delete
End Sub


I have attached sample files so you can see what you are looking at; the zero columns must be in the soucre sheets due to possible movements and additions.

Thanks again