Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me with this marco
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me with this marco
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 "JBoyer" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me with this marco
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. "JBoyer" wrote: 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 "JBoyer" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me with this marco
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. "JLGWhiz" wrote: 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. "JBoyer" wrote: 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 "JBoyer" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me with this marco
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. "JBoyer" wrote: 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. "JLGWhiz" wrote: 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. "JBoyer" wrote: 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 "JBoyer" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me with this marco
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 "JBoyer" wrote: 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 |
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) | |||
help me with this marco | Excel Programming | |||
marco | Excel Programming |