Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
Hello,
I have some code that adds the contents of one sheet to the end of another. How do I correctly select the first empty cell below the first sheet that I want to add data to? Something like ActiveCell.SpecialCells(xlLastCell).Select 'Move to bottom of range ^^^ This is where I need work. Can I simply perform a cursor key move down one cell? How do I code cursor key operations? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
Hi,
It is very unlikely that you will need to select a cell to do what you want but here's a couple of methods Select empty cell after last used cell in col A Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1).Select Select empty cell in Col A below the last use cell on worksheet Range("A" & Cells.SpecialCells(xlLastCell).Row + 1).Select -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Copacetic" wrote: Hello, I have some code that adds the contents of one sheet to the end of another. How do I correctly select the first empty cell below the first sheet that I want to add data to? Something like ActiveCell.SpecialCells(xlLastCell).Select 'Move to bottom of range ^^^ This is where I need work. Can I simply perform a cursor key move down one cell? How do I code cursor key operations? . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
See also
http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike H" wrote in message ... Hi, It is very unlikely that you will need to select a cell to do what you want but here's a couple of methods Select empty cell after last used cell in col A Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1).Select Select empty cell in Col A below the last use cell on worksheet Range("A" & Cells.SpecialCells(xlLastCell).Row + 1).Select -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Copacetic" wrote: Hello, I have some code that adds the contents of one sheet to the end of another. How do I correctly select the first empty cell below the first sheet that I want to add data to? Something like ActiveCell.SpecialCells(xlLastCell).Select 'Move to bottom of range ^^^ This is where I need work. Can I simply perform a cursor key move down one cell? How do I code cursor key operations? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
ActiveCell.SpecialCells(xlLastCell).Offset(1,0).Se lect
Or like this: Range("A1").End(xlDown).Offset(1,0).Select Regards, Per On 18 Apr., 10:23, Copacetic wrote: Hello, *I have some code that adds the contents of one sheet to the end of another. How do I correctly select the first empty cell below the first sheet that I want to add data to? * Something like * * ActiveCell.SpecialCells(xlLastCell).Select 'Move to bottom of range * ^^^ This is where I need work. * Can I simply perform a cursor key move down one cell? * How do I code cursor key operations? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
Right on the money. Thanks. On Sun, 18 Apr 2010 02:24:34 -0700 (PDT), Per Jessen wrote: ActiveCell.SpecialCells(xlLastCell).Offset(1,0).S elect Or like this: Range("A1").End(xlDown).Offset(1,0).Select Regards, Per On 18 Apr., 10:23, Copacetic wrote: Hello, *I have some code that adds the contents of one sheet to the end of another. How do I correctly select the first empty cell below the first sheet that I want to add data to? * Something like * * ActiveCell.SpecialCells(xlLastCell).Select 'Move to bottom of range * ^^^ This is where I need work. * Can I simply perform a cursor key move down one cell? * How do I code cursor key operations? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
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 -- Rick (MVP - Excel) "Mike H" wrote in message ... Hi, It is very unlikely that you will need to select a cell to do what you want but here's a couple of methods Select empty cell after last used cell in col A Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1).Select Select empty cell in Col A below the last use cell on worksheet Range("A" & Cells.SpecialCells(xlLastCell).Row + 1).Select -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Copacetic" wrote: Hello, I have some code that adds the contents of one sheet to the end of another. How do I correctly select the first empty cell below the first sheet that I want to add data to? Something like ActiveCell.SpecialCells(xlLastCell).Select 'Move to bottom of range ^^^ This is where I need work. Can I simply perform a cursor key move down one cell? How do I code cursor key operations? . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
In case you selected the SpecialCells version to use, make sure you read the
article that Ron linked to in his posting to see why that might not be a good idea. Per's second method will work fine as long as there are **no** blank cells between A1 and the last piece of data in Column A. -- Rick (MVP - Excel) "Copacetic" wrote in message ... Right on the money. Thanks. On Sun, 18 Apr 2010 02:24:34 -0700 (PDT), Per Jessen wrote: ActiveCell.SpecialCells(xlLastCell).Offset(1,0). Select Or like this: Range("A1").End(xlDown).Offset(1,0).Select Regards, Per On 18 Apr., 10:23, Copacetic wrote: Hello, I have some code that adds the contents of one sheet to the end of another. How do I correctly select the first empty cell below the first sheet that I want to add data to? Something like ActiveCell.SpecialCells(xlLastCell).Select 'Move to bottom of range ^^^ This is where I need work. Can I simply perform a cursor key move down one cell? How do I code cursor key operations? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
"Per Jessen" wrote in message
... ActiveCell.SpecialCells(xlLastCell).Offset(1,0).Se lect Or like this: Range("A1").End(xlDown).Offset(1,0).Select Regards, Per On 18 Apr., 10:23, Copacetic wrote: Hello, I have some code that adds the contents of one sheet to the end of another. How do I correctly select the first empty cell below the first sheet that I want to add data to? sheet that has to have data [rows] added usualy need to have some known column structure, otherwise it is mismatched, wrong, invalid i use framework something like this: ---- row=1 bad=0 lastrow=0 do while bad<100 'max number of bads in sequence if not valid_row(row) then 'function to test row validity/emptyness bad=bad+1 else bad=0 lastrow=row end if row=row+1 loop ----- does such a test make sense, or is just time & cpu vasting? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
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" 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
Man, when you guys abandon a thread, you really never look back.
On Sun, 18 Apr 2010 11:27:00 -0700, Copacetic 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" 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
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 wrote: Man, when you guys abandon a thread, you really never look back. On Sun, 18 Apr 2010 11:27:00 -0700, Copacetic 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" 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 - |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
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 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 wrote: Man, when you guys abandon a thread, you really never look back. On Sun, 18 Apr 2010 11:27:00 -0700, Copacetic 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" 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 - |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
On Wed, 21 Apr 2010 12:45:42 -0700 (PDT), Per Jessen
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? |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
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 wrote: On Wed, 21 Apr 2010 12:45:42 -0700 (PDT), Per Jessen 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? |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
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 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 wrote: On Wed, 21 Apr 2010 12:45:42 -0700 (PDT), Per Jessen 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? |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
Selecting objects before manipulating them should not be the issue...
Using Select is just slowing down things. Maybe a typo in an object reference ? To help you further, let me know which line is highlighted when you click Debug. Or you can mail me a sample workbook if you want me to have a look at it. //Per On 27 Apr., 14:46, Copacetic wrote: *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 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 wrote: On Wed, 21 Apr 2010 12:45:42 -0700 (PDT), Per Jessen 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?- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
On Tue, 27 Apr 2010 06:01:57 -0700 (PDT), Per Jessen
wrote: Selecting objects before manipulating them should not be the issue... Using Select is just slowing down things. Maybe a typo in an object reference ? To help you further, let me know which line is highlighted when you click Debug. Or you can mail me a sample workbook if you want me to have a look at it. If you are high bandwidth, there is one stripped, and one with some image files... The rest is the same. It has hard codings to the root of the C: drive. The DVD logos are "buttons" for bringing up the DVD cover art. The actor button, and the "main lookup" button should be obvious (the "Actors" banner, and simply pressing enter after entering the movie number from the "listing" sheet. The Actors and director will not look up until the retrieval macros grab the files. Then they should work as well. The issue however is with the conversion code, and the previously discussed and worked on sub (bookmorpher). http://www.mediafire.com/file/gkqiym...D_DB_Small.rar http://www.mediafire.com/file/n0z1mz...DVD_DB_Big.rar |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
Good you let me see your file, else I couldn't find the error....
While testing you code I got a 'Subscript out of range error'... The issue is that you have all your macros in 'ThisWorkbook' module, where sheet references are seen as references to sheets in 'ThisWorkbook'. The code sheet for ThisWorkbook should only contain event macros related to ThisWorkbook. You macros should be in a general module (Insert Module). I just inserted my macro into a general module (Module1), and assigned the button on the Master Pane sheet to call this macro, and it worked as expected! Best regards, Per On 28 Apr., 00:11, Copacetic wrote: On Tue, 27 Apr 2010 06:01:57 -0700 (PDT), Per Jessen wrote: Selecting objects before manipulating them should not be the issue... Using Select is just slowing down things. Maybe a typo in an object reference ? To help you further, let me know which line is highlighted when you click Debug. Or you can mail me a sample workbook if you want me to have a look at it. * If you are high bandwidth, there is one stripped, and one with some image files... *The rest is the same. *It has hard codings to the root of the C: drive. * The DVD logos are "buttons" for bringing up the DVD cover art. * The actor button, and the "main lookup" button should be obvious (the "Actors" banner, and simply pressing enter after entering the *movie number from the "listing" sheet. *The Actors and director will not look up until the retrieval macros grab the files. *Then they should work as well. *The issue however is with the conversion code, and the previously discussed and worked on sub (bookmorpher). http://www.mediafire.com/file/gkqiym...D_DB_Small.rar http://www.mediafire.com/file/n0z1mz...DVD_DB_Big.rar |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Move to bottom of range"
Thankx, Per, I'll have a look at it segregated that way.
On Thu, 29 Apr 2010 00:10:04 -0700 (PDT), Per Jessen wrote: Good you let me see your file, else I couldn't find the error.... While testing you code I got a 'Subscript out of range error'... The issue is that you have all your macros in 'ThisWorkbook' module, where sheet references are seen as references to sheets in 'ThisWorkbook'. The code sheet for ThisWorkbook should only contain event macros related to ThisWorkbook. You macros should be in a general module (Insert Module). I just inserted my macro into a general module (Module1), and assigned the button on the Master Pane sheet to call this macro, and it worked as expected! Best regards, Per On 28 Apr., 00:11, Copacetic wrote: On Tue, 27 Apr 2010 06:01:57 -0700 (PDT), Per Jessen wrote: Selecting objects before manipulating them should not be the issue... Using Select is just slowing down things. Maybe a typo in an object reference ? To help you further, let me know which line is highlighted when you click Debug. Or you can mail me a sample workbook if you want me to have a look at it. * If you are high bandwidth, there is one stripped, and one with some image files... *The rest is the same. *It has hard codings to the root of the C: drive. * The DVD logos are "buttons" for bringing up the DVD cover art. * The actor button, and the "main lookup" button should be obvious (the "Actors" banner, and simply pressing enter after entering the *movie number from the "listing" sheet. *The Actors and director will not look up until the retrieval macros grab the files. *Then they should work as well. *The issue however is with the conversion code, and the previously discussed and worked on sub (bookmorpher). http://www.mediafire.com/file/gkqiym...D_DB_Small.rar http://www.mediafire.com/file/n0z1mz...DVD_DB_Big.rar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turn off "CALCULATE" on bottom of Excel worksheet. near "Ready" | Excel Discussion (Misc queries) | |||
extract range with same weeks and move to "calendar" | Excel Programming | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
compare ranges in different workbooks and copy "not matching values" at bottom of range 1 | Excel Programming | |||
copy a range of cells (a row) to the bottom of a "list" | Excel Programming |