"Move to bottom of range" [Programming]

Prev: A formula for subtotals against each category
Next: fractions to decimal

From: Per Jessen on 21 Apr 2010 15:45

Give this a go (not tested):

Sub BookMorpher()
Dim DestCell As Range
'this sub opens the downloaded 2k3 file, saves it as a 2k7 format,
'then concatenates the contents of two segmented sheets of data
'onto the tail of the first sheet, then deletes the two segments

Application.ScreenUpdating = False

' Open the downloaded 2ks version file
Workbooks.Open Filename:="C:\DVD_Image_Database\temp\dvdlist.xls"

'Save as then re-open the 2k7 file format workbook
'this is required as the saved-as sheet will not
'accept the pastes until it has been closed and re-opened
ActiveWorkbook.SaveAs Filename:="C:\DVD_Image_Database\dvdlist.xlsx",
_
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Workbooks.Open Filename:="C:\DVD_Image_Database\dvdlist.xlsx"

'shift the ID column to the correct location for all three sheets
With Sheets("a-f")
.Name = "DVDs"
.Columns("N:N").Cut
.Columns("A:A").Insert Shift:=xlToRight
.Rows("1:1").Delete Shift:=xlUp
End With
With Sheets("g-o")
.Columns("N:N").Cut
.Columns("A:A").Insert Shift:=xlToRight
.Rows("1:1").Delete Shift:=xlUp
End With
With Sheets("p-z")
.Columns("N:N").Cut
.Columns("A:A").Insert Shift:=xlToRight
.Rows("1:1").Delete Shift:=xlUp
End With

Sheets("DVDs").Select
Set DestCell = Range("A1").End(xlDown).Offset(1, 0)

'Copy the segmented sheets to the end of the first sheet
With Sheets("g-o")
.Range("A1", .Range("A1").End(xlDown)).Copy Destination:=DestCell
End With

Set DestCell = Range("A1").End(xlDown).Offset(1, 0)
With Sheets("p-z")
.Range("A1", .Range("A1").End(xlDown)).Copy DestCell
End With

'delete the un-needed segmented sheets, leaving only the primary data
set
Application.DisplayAlerts = False
Sheets("p-z").Delete
Sheets("g-o").Delete
Application.DisplayAlerts = True

'Range("A1", Range("A1").End(xlDown)).Select
Application.ScreenUpdating = True
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

Regards,
Per

On 20 Apr., 04:22, Copacetic <Copace...(a)iseverythingalright.org>
wrote:
> Man, when you guys abandon a thread, you really never look back.
>
> On Sun, 18 Apr 2010 11:27:00 -0700, Copacetic
>
>
>
> <Copace...(a)iseverythingalright.org> wrote:
> > What I am after is placing the cursor in the desired cell of the
> >desired sheet so that when I copy and paste to it, I am certain of the
> >location of the paste.
>
> > What I do is open a three sheet 2k3 format workbook and delete the
> >first row of all three, then save it in 2k7 format so I can increase the
> >data set size, then close and re-open that to allow it to actually
> >function in 2k7 mode, then move the cursor to the end of the first sheet,
> >then mark and copy the second, and paste it into the first at that end
> >point, the repeat with the third, then delete the second and third, then
> >save again
>
> > My current code fails because being from a recorded macro, it carries
> >the direct cell reference instead of my cursor moves to get to the end of
> >the current data set before I paste, and that does not work when I open a
> >file that has more records in it than the previous had (or less).
>
> > My current code uses the last suggestion I got in this thread before,
> >and is untried, but here 'tis, see if you can see any easier way. The
> >database itself is at:
>
> >http://www.hometheaterinfo.com/download/dvdlist.zip
>
> > It shrinks from 46 MB to 19 MB after I do this conversion. It also
> >makes it easier to query and use as a flat file database.
>
> >The macro is quoted below:
>
> >Sub BookMorpher()
> >'this sub opens the downloaded 2k3 file, saves it as a 2k7 format,
> >'then concatenates the contents of two segmented sheets of data
> >'onto the tail of the first sheet, then deletes the two segments
>
> >' Open the downloaded 2ks version file
> > Workbooks.Open Filename:="C:\DVD_Image_Database\temp\dvdlist.xls"
> > Sheets("a-f").Select
> >'rename the first sheet
> > Sheets("a-f").Name = "DVDs"
> >'shift the ID column to the correct location for all three sheets
> > Columns("N:N").Select
> > Selection.Cut
> > Columns("A:A").Select
> > Selection.Insert Shift:=xlToRight
> > Sheets("g-o").Select
> > Columns("N:N").Select
> > Selection.Cut
> > Columns("A:A").Select
> > Selection.Insert Shift:=xlToRight
> > Sheets("p-z").Select
> > Columns("N:N").Select
> > Selection.Cut
> > Columns("A:A").Select
> > Selection.Insert Shift:=xlToRight
> >'delete the top row of all three sheets
> > Rows("1:1").Select
> > Selection.Delete Shift:=xlUp
> > Sheets("g-o").Select
> > Rows("1:1").Select
> > Selection.Delete Shift:=xlUp
> > Sheets("DVDs").Select
> > Rows("1:1").Select
> > Selection.Delete Shift:=xlUp
> >'Move to the end of the DVDs sheet
> > Range("A1").Select
> > Range("A1").End(xlDown).Offset(1, 0).Select
> >'Save as then re-open the 2k7 file format workbook
> >'this is required as the saved-as sheet will not
> >'accept the pastes until it has been closed and re-opened
> > ActiveWorkbook.SaveAs Filename:="C:\DVD_Image_Database\dvdlist..xlsx",
> >_
> > FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
> > Workbooks.Open Filename:="C:\DVD_Image_Database\dvdlist.xlsx"
> >'Copy the segmented sheets to the end of the first sheet
> > Sheets("g-o").Select
> > Range("A1").Select
> > Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Sheets("DVDs").Select
> > Range("A1").Select
> > Range("A1").End(xlDown).Offset(1, 0).Select
> > ActiveSheet.Paste
> > Range("A1").Select
> > Range("A1").End(xlDown).Offset(1, 0).Select
> > Sheets("p-z").Select
> > Range("A1").Select
> > Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Sheets("DVDs").Select
> > ActiveSheet.Paste
> >'delete the un-needed segmented sheets, leaving only the primary data set
> > Sheets("p-z").Select
> > Application.CutCopyMode = False
> > ActiveWindow.SelectedSheets.Delete
> > Sheets("g-o").Select
> > ActiveWindow.SelectedSheets.Delete
> > Range("A1").Select
> > Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> > ActiveWorkbook.Save
> > ActiveWindow.Close
> >End Sub
>
> >On Sun, 18 Apr 2010 10:42:03 -0400, "Rick Rothstein"
> ><rick.newsNO.S...(a)NO.SPAMverizon.net> wrote:
>
> >>> Select empty cell after last used cell in col A
> >>> Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1).Select
>
> >>The above can be shortened to this...
>
> >>Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
>
> >>> Select empty cell in Col A below the last use cell on worksheet
> >>> Range("A" & Cells.SpecialCells(xlLastCell).Row + 1).Select
>
> >>As Ron points out in the article he linked to, SpecialCells(xlLastCell) can
> >>give you the wrong cell reference under certain circumstances. If you are
> >>looking for a second method, though, then here is an alternative which will
> >>work...
>
> >>Columns("A").Find("*", , xlFormulas, , xlRows, xlPrevious).Offset(1).Select
>
> >>It should be noted that both of the methods I posted find the empty cell
> >>after the last cell with a value or formula EVEN IF that formula is
> >>displaying the empty cell. If one would want to find the empty **looking**
> >>cell (that is, the cell that is either empty or displaying the empty string)
> >>located after the last displayed, non-empty value, you could use this last
> >>method using xlValues in place of xlFormulas...
>
> >>Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Offset(1).Select- Skjul tekst i anførselstegn -
>
> - Vis tekst i anførselstegn -

From: Copacetic on 21 Apr 2010 22:52

The problems I see right at the start is that entire column or row
selection in 2k7 or 2k10 is a HUGE datagram of blank data, and even gives
a warning about it. I need to only select the used data area.

Oh wait... I see, that is just the ID column move. Nevermind... :-)

The other thing I saw is that after the save-as, it MUST be closed,
then re-opened. You issue the open workbook, but forgot to close it
after the save-as.

I like the "With End With" framing though.

Looks flawless. I'll insert the aforementioned close statement, and
check it out.

Thanks. I am learning a bit about the conventions used in VB.

I was going to DL the visual studio beta trial thing. Do you think
that would be a good learning tool for me,. or would books be better?

Thanks again, btw.

On Wed, 21 Apr 2010 12:45:42 -0700 (PDT), Per Jessen
<perjessen69(a)hotmail.com> wrote:

>Give this a go (not tested):
>
>Sub BookMorpher()
>Dim DestCell As Range
>'this sub opens the downloaded 2k3 file, saves it as a 2k7 format,
>'then concatenates the contents of two segmented sheets of data
>'onto the tail of the first sheet, then deletes the two segments
>
>Application.ScreenUpdating = False
>
>' Open the downloaded 2ks version file
>Workbooks.Open Filename:="C:\DVD_Image_Database\temp\dvdlist.xls"
>
>'Save as then re-open the 2k7 file format workbook
>'this is required as the saved-as sheet will not
>'accept the pastes until it has been closed and re-opened
>ActiveWorkbook.SaveAs Filename:="C:\DVD_Image_Database\dvdlist.xlsx",
>_
> FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
>Workbooks.Open Filename:="C:\DVD_Image_Database\dvdlist.xlsx"
>
>'shift the ID column to the correct location for all three sheets
>With Sheets("a-f")
> .Name = "DVDs"
> .Columns("N:N").Cut
> .Columns("A:A").Insert Shift:=xlToRight
> .Rows("1:1").Delete Shift:=xlUp
>End With
>With Sheets("g-o")
> .Columns("N:N").Cut
> .Columns("A:A").Insert Shift:=xlToRight
> .Rows("1:1").Delete Shift:=xlUp
>End With
>With Sheets("p-z")
> .Columns("N:N").Cut
> .Columns("A:A").Insert Shift:=xlToRight
> .Rows("1:1").Delete Shift:=xlUp
>End With
>
>Sheets("DVDs").Select
>Set DestCell = Range("A1").End(xlDown).Offset(1, 0)
>
>'Copy the segmented sheets to the end of the first sheet
>With Sheets("g-o")
> .Range("A1", .Range("A1").End(xlDown)).Copy Destination:=DestCell
>End With
>
>Set DestCell = Range("A1").End(xlDown).Offset(1, 0)
>With Sheets("p-z")
> .Range("A1", .Range("A1").End(xlDown)).Copy DestCell
>End With
>
>'delete the un-needed segmented sheets, leaving only the primary data
>set
>Application.DisplayAlerts = False
>Sheets("p-z").Delete
>Sheets("g-o").Delete
>Application.DisplayAlerts = True
>
>'Range("A1", Range("A1").End(xlDown)).Select
>Application.ScreenUpdating = True
>ActiveWorkbook.Save
>ActiveWindow.Close
>End Sub
>
>Regards,
>Per
>
>On 20 Apr., 04:22, Copacetic <Copace...(a)iseverythingalright.org>
>wrote:
>> Man, when you guys abandon a thread, you really never look back.
>>
>> On Sun, 18 Apr 2010 11:27:00 -0700, Copacetic
>>
>>
>>
>> <Copace...(a)iseverythingalright.org> wrote:
>> > �What I am after is placing the cursor in the desired cell of the
>> >desired sheet so that when I copy and paste to it, I am certain of the
>> >location of the paste.
>>
>> > �What I do is open a three sheet 2k3 format workbook and delete the
>> >first row of all three, then save it in 2k7 format so I can increase the
>> >data set size, �then close and re-open that to allow it to actually
>> >function in 2k7 mode, then move the cursor to the end of the first sheet,
>> >then mark and copy the second, and paste it into the first at that end
>> >point, the repeat with the third, then delete the second and third, then
>> >save again
>>
>> > �My current code fails because being from a recorded macro, it carries
>> >the direct cell reference instead of my cursor moves to get to the end of
>> >the current data set before I paste, and that does not work when I open a
>> >file that has more records in it than the previous had (or less).
>>
>> > My current code uses the last suggestion I got in this thread before,
>> >and is untried, but here 'tis, see if you can see any easier way. �The
>> >database itself is at:
>>
>> >http://www.hometheaterinfo.com/download/dvdlist.zip
>>
>> > �It shrinks from 46 MB to 19 MB after I do this conversion. It also
>> >makes it easier to query and use as a flat file database.
>>
>> >The macro is quoted below:
>>
>> >Sub BookMorpher()
>> >'this sub opens the downloaded 2k3 file, saves it as a 2k7 format,
>> >'then concatenates the contents of two segmented sheets of data
>> >'onto the tail of the first sheet, then deletes the two segments
>>
>> >' Open the downloaded 2ks version file
>> > � �Workbooks.Open Filename:="C:\DVD_Image_Database\temp\dvdlist.xls"
>> > � �Sheets("a-f").Select
>> >'rename the first sheet
>> > � �Sheets("a-f").Name = "DVDs"
>> >'shift the ID column to the correct location for all three sheets
>> > � �Columns("N:N").Select
>> > � �Selection.Cut
>> > � �Columns("A:A").Select
>> > � �Selection.Insert Shift:=xlToRight
>> > � �Sheets("g-o").Select
>> > � �Columns("N:N").Select
>> > � �Selection.Cut
>> > � �Columns("A:A").Select
>> > � �Selection.Insert Shift:=xlToRight
>> > � �Sheets("p-z").Select
>> > � �Columns("N:N").Select
>> > � �Selection.Cut
>> > � �Columns("A:A").Select
>> > � �Selection.Insert Shift:=xlToRight
>> >'delete the top row of all three sheets
>> > � �Rows("1:1").Select
>> > � �Selection.Delete Shift:=xlUp
>> > � �Sheets("g-o").Select
>> > � �Rows("1:1").Select
>> > � �Selection.Delete Shift:=xlUp
>> > � �Sheets("DVDs").Select
>> > � �Rows("1:1").Select
>> > � �Selection.Delete Shift:=xlUp
>> >'Move to the end of the DVDs sheet
>> > � �Range("A1").Select
>> > � �Range("A1").End(xlDown).Offset(1, 0).Select
>> >'Save as then re-open the 2k7 file format workbook
>> >'this is required as the saved-as sheet will not
>> >'accept the pastes until it has been closed and re-opened
>> > � �ActiveWorkbook.SaveAs Filename:="C:\DVD_Image_Database\dvdlist.xlsx",
>> >_
>> > � � � �FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
>> > � �Workbooks.Open Filename:="C:\DVD_Image_Database\dvdlist.xlsx"
>> >'Copy the segmented sheets to the end of the first sheet
>> > � �Sheets("g-o").Select
>> > � �Range("A1").Select
>> > � �Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
>> > � �Application.CutCopyMode = False
>> > � �Selection.Copy
>> > � �Sheets("DVDs").Select
>> > � �Range("A1").Select
>> > � �Range("A1").End(xlDown).Offset(1, 0).Select
>> > � �ActiveSheet.Paste
>> > � �Range("A1").Select
>> > � �Range("A1").End(xlDown).Offset(1, 0).Select
>> > � �Sheets("p-z").Select
>> > � �Range("A1").Select
>> > � �Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
>> > � �Application.CutCopyMode = False
>> > � �Selection.Copy
>> > � �Sheets("DVDs").Select
>> > � �ActiveSheet.Paste
>> >'delete the un-needed segmented sheets, leaving only the primary data set
>> > � �Sheets("p-z").Select
>> > � �Application.CutCopyMode = False
>> > � �ActiveWindow.SelectedSheets.Delete
>> > � �Sheets("g-o").Select
>> > � �ActiveWindow.SelectedSheets.Delete
>> > � �Range("A1").Select
>> > � �Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
>> > � �ActiveWorkbook.Save
>> > � �ActiveWindow.Close
>> >End Sub
>>
>> >On Sun, 18 Apr 2010 10:42:03 -0400, "Rick Rothstein"
>> ><rick.newsNO.S...(a)NO.SPAMverizon.net> wrote:
>>
>> >>> Select empty cell after last used cell in col A
>> >>> Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1).Select
>>
>> >>The above can be shortened to this...
>>
>> >>Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
>>
>> >>> Select empty cell in Col A below the last use cell on worksheet
>> >>> Range("A" & Cells.SpecialCells(xlLastCell).Row + 1).Select
>>
>> >>As Ron points out in the article he linked to, SpecialCells(xlLastCell) can
>> >>give you the wrong cell reference under certain circumstances. If you are
>> >>looking for a second method, though, then here is an alternative which will
>> >>work...
>>
>> >>Columns("A").Find("*", , xlFormulas, , xlRows, xlPrevious).Offset(1).Select
>>
>> >>It should be noted that both of the methods I posted find the empty cell
>> >>after the last cell with a value or formula EVEN IF that formula is
>> >>displaying the empty cell. If one would want to find the empty **looking**
>> >>cell (that is, the cell that is either empty or displaying the empty string)
>> >>located after the last displayed, non-empty value, you could use this last
>> >>method using xlValues in place of xlFormulas...
>>
>> >>Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Offset(1).Select- Skjul tekst i anf�rselstegn -
>>
>> - Vis tekst i anf�rselstegn -

From: Copacetic on 21 Apr 2010 23:12

On Wed, 21 Apr 2010 12:45:42 -0700 (PDT), Per Jessen
<perjessen69(a)hotmail.com> wrote:

>Give this a go (not tested)

I keep getting "subscript out of range" exceptions.

It did with mine as well, so it isn't anything you did.

When the screen comes back up (you shut it off) the top header rows are
still in place as are the ID columns, so it is pretty early on. It does
do the save-as because I get prompted on the overwrite, and it does open
it back up after I close it.

Is there a way to step through a script one line at a time?

From: Per Jessen on 22 Apr 2010 11:18

Two things;

Use F8 to step through the code.

When you get the error message, click debug and see which line is
highligted in yellow.

Hopes this helps.
....
Per

On 22 Apr., 05:12, Copacetic <Copace...(a)iseverythingalright.org>
wrote:
> On Wed, 21 Apr 2010 12:45:42 -0700 (PDT), Per Jessen
>
> <perjesse...(a)hotmail.com> wrote:
> >Give this a go (not tested)
>
> I keep getting "subscript out of range" exceptions.
>
> It did with mine as well, so it isn't anything you did.
>
> When the screen comes back up (you shut it off) the top header rows are
> still in place as are the ID columns, so it is pretty early on. It does
> do the save-as because I get prompted on the overwrite, and it does open
> it back up after I close it.
>
> Is there a way to step through a script one line at a time?

From: Copacetic on 27 Apr 2010 08:46

The errors have to do with making calls to unselected objects... I
think.

On Thu, 22 Apr 2010 08:18:36 -0700 (PDT), Per Jessen
<perjessen69(a)hotmail.com> wrote:

>Two things;
>
>Use F8 to step through the code.
>
>When you get the error message, click debug and see which line is
>highligted in yellow.
>
>Hopes this helps.
>...
>Per
>
>
>On 22 Apr., 05:12, Copacetic <Copace...(a)iseverythingalright.org>
>wrote:
>> On Wed, 21 Apr 2010 12:45:42 -0700 (PDT), Per Jessen
>>
>> <perjesse...(a)hotmail.com> wrote:
>> >Give this a go (not tested)
>>
>> � I keep getting "subscript out of range" exceptions.
>>
>> � It did with mine as well, so it isn't anything you did.
>>
>> �When the screen comes back up (you shut it off) the top header rows are
>> still in place as are the ID columns, so it is pretty early on. �It does
>> do the save-as because I get prompted on the overwrite, and it does open
>> it back up after I close it.
>>
>> � Is there a way to step through a script one line at a time?

First | Prev | Next | Last
Pages: 1 2 3 4
Prev: A formula for subtotals against each category
Next: fractions to decimal