Help with this marco

  • Thread starter Thread starter JBoyer
  • Start date Start date
J

JBoyer

Sub DeleteSemester()
Range("N1:AR100").Cut Cells(305, "N")
Dim lr As Long, x As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
x = lr - 5
ActiveSheet.Rows(x & ":" & lr - 1).Delete
Range("N300:AR400").Cut Cells(1, "N")
End Sub

How can I make this marco so that if the last row in columns A:M is row 6 or
less it does not run.
 
Hello, Try this

Sub DeleteSemester()
Dim i As Integer
Dim cell As Range
i = 1
For Each cell In Range("A65536:M65536")
If cell.End(xlUp).Row > i Then i = cell.End(xlUp).Row
Next cell
If i <= 6 Then Exit Sub
Range("N1:AR100").Cut Cells(305, "N")
Dim lr As Long, x As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
x = lr - 5
ActiveSheet.Rows(x & ":" & lr - 1).Delete
Range("N300:AR400").Cut Cells(1, "N")
End Sub
 
Maybe

Sub nn()
For cols = 1 To 13
Cells(1, cols).Select
If ActiveSheet.Cells(Rows.Count, cols).End(xlUp).Row < 6 Then
Exit Sub
Else
' Your code
End If
Next
End Sub

Mike
 
Try it this way (notice I reduced your middle 4 lines down to a single line
of code)...

Sub DeleteSemester()
With ActiveSheet
If Application.WorksheetFunction.CountA( _
.Range("A7:M" & Rows.Count)) = 0 Then
.Range("N1:AR100").Cut .Cells(305, "N")
.Cells(Rows.Count, 1).End(xlUp).EntireRow.Offset(-5).Resize(5).Delete
.Range("N300:AR400").Cut .Cells(1, "N")
End If
End With
End Sub

Rick
 
All the replies look good guys, thanks for the help. I tried Rick's first,
but I failed to explain one of my cateria, and his being the simplest code
didn't cover my criteria. Not his fault though, my fault for not explaining.
Luckily though after rereading the replies I found that GTVT covered what I
need, so it is what I am currently using. Anyway thanks for all your help
guys, including you Mike, who I didn't mention.
 
Back
Top