Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this marco
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this marco
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this marco
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 "JBoyer" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this marco
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 "JBoyer" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this marco
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. "GTVT06" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Marco | Excel Programming | |||
Marco Help | Excel Programming | |||
Marco Help | Excel Discussion (Misc queries) | |||
marco help | Excel Programming | |||
help me with this marco | Excel Programming |