Help with this marco

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.
 
G

GTVT06

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
 
M

Mike H

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
J

JBoyer

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top