Can someone help me with this marco

J

JBoyer

Not familiar with marcos can someone tell me what I did wrong. Or even better
and help make it right.

Sub DeleteSemester()
Dim lr As Long, x As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
x = lr - 5
Range("BI2:BO26").Copy Cells(200, BI)
ActiveSheet.Rows(x & ":" & lr - 1).Delete
Range("BI195:BJ195").Copy Cells(2, Y)
Range("BI195:BJ195").Copy Cells(2, BI)
Range("BI195:BJ195").Delete
Range("BM195:BO195").Copy Cells(2, AK)
Range("BM195:BO195").Copy Cells(2, BM)
Range("BM195:BO195").Delete
End Sub
 
J

JBoyer

Actually I meant something like this, which still isn't right but more of
what I want.

Sub DeleteSemester()
Dim lr As Long, x As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
x = lr - 5
Range("BI2:BO26").Copy Cells(200, BI)
ActiveSheet.Rows(x & ":" & lr - 1).Delete
Range("BI195:BJ219").Copy Cells(2, Y)
Range("BI195:BJ219").Copy Cells(2, BI)
Range("BI195:BJ219").Delete
Range("BM195:BO219").Copy Cells(2, AK)
Range("BM195:BO219").Copy Cells(2, BM)
Range("BM195:BO219").Delete
End Sub
 
J

JLGWhiz

Perhaps if you explained what you are trying to do, someone could help. The
code you posted is too ambiguous to tell exactly what you expect as a result.
 
J

JBoyer

Well this is the original marco,

Sub DeleteFiveRows()
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
End Sub

But in addition to that I am trying to copy cells BI2 through BO26 and paste
them out of the way in what I said would be BI200. Then since 5 rows are
being deleted the cells I just copied would be moved down five rows. So I
would like to copy BI195:BJ219 and paste it in y2 and bi2. I would also like
to copy bm195:bo219 and paste it in ak2 and bm2. then after that cells
BI195:BO219 can be deleted.
 
J

JLGWhiz

Sub DeleteSemester()
Dim lr As Long, x As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
x = lr - 5
Range("BI2:BO26").Copy Cells(200, BI)
ActiveSheet.Rows(x & ":" & lr - 1).Delete
' The column designator is Cells(2, Y) needs
' to be in quote marks like: Cells(2, "Y") and
' that applies to all the other column references
' using the Cells property to specify the range.
Range("BI195:BJ195").Copy Cells(2, Y)
Range("BI195:BJ195").Copy Cells(2, BI)
Range("BI195:BJ195").Delete
Range("BM195:BO195").Copy Cells(2, AK)
Range("BM195:BO195").Copy Cells(2, BM)
Range("BM195:BO195").Delete
End Sub
 
J

JLGWhiz

OK, I think if you make the corrections I have noted, that your
code should work. The comments below should help you to better
understand what the code is doing.

1. Let's start with the deletion of the five rows.
The default for the rows shift is xlUP. So unless
you specify otherwise, ActiveSheet.Rows(x & ":" & lr -1).Delete
will cause the last row and all rows below to shift up.

2. When copying a range to a location on the same sheet and
you know the address of the range you want to paste to, it is
more efficient to use the Range("A1") format than the Cells(1. 1)
format. Cells is a property that uses the row and column matrix
to form a range location, whereas the Range("A1") is a Range
object and is more specific in nature. The Cells(lr, 1) where
lr is a variable row is a good format to use for looping or or
some other method where the row number is subject to change.

3. When you use the Cells(1, 1) format and you use the Column
letters rather than the number, i.e. Cells(1, "AI"), note that
the "AI" is enclosed in quote marks so that VBA will know it is
a string value and will treat it as a column designation rather
than a variable.
 

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

Similar Threads


Top