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
|
Similar Threads
