Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Row - Select to end of sheet
Hello!
I am trying to create a section of macro to find the first row of data from the bottom of the sheet up, select one row down from the ActiveRow to the end of the sheet, then Clear the content of the selected rows. The row location of the data is always different. I have tried the folowing, but it does not work. I would greatly appreciate help! Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Rows("1:65536").EntireRow.Select Selection.Clear |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Row - Select to end of sheet
Once you have selected a cell, you have defined the active row. This macro
will clear all the cells in all the rows from the row below the active row thru row 65536. You must ,however, select the last row of data before running the macro. Sub clearit() i = Selection.Row + 1 Range(Cells(i, 1), Cells(65536, 255)).Select Selection.Clear End Sub -- Gary''s Student "Lost in Alabama" wrote: Hello! I am trying to create a section of macro to find the first row of data from the bottom of the sheet up, select one row down from the ActiveRow to the end of the sheet, then Clear the content of the selected rows. The row location of the data is always different. I have tried the folowing, but it does not work. I would greatly appreciate help! Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Rows("1:65536").EntireRow.Select Selection.Clear |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Row - Select to end of sheet
Range(ActiveCell.Offset(1, 0), Range("A" &
Rows.Count).End(xlUp)).EntireRow.Clear -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Lost in Alabama" wrote in message ... Hello! I am trying to create a section of macro to find the first row of data from the bottom of the sheet up, select one row down from the ActiveRow to the end of the sheet, then Clear the content of the selected rows. The row location of the data is always different. I have tried the folowing, but it does not work. I would greatly appreciate help! Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Rows("1:65536").EntireRow.Select Selection.Clear |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Row - Select to end of sheet
Hello Lost In Alabama, Here is another version... Dim EndCell As Range Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp) ActiveSheet.Range(ActiveCell, EndCell).ClearContents Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=509437 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Row - Select to end of sheet
Thanks Gary's Student. I will try this.
"Gary''s Student" wrote: Once you have selected a cell, you have defined the active row. This macro will clear all the cells in all the rows from the row below the active row thru row 65536. You must ,however, select the last row of data before running the macro. Sub clearit() i = Selection.Row + 1 Range(Cells(i, 1), Cells(65536, 255)).Select Selection.Clear End Sub -- Gary''s Student "Lost in Alabama" wrote: Hello! I am trying to create a section of macro to find the first row of data from the bottom of the sheet up, select one row down from the ActiveRow to the end of the sheet, then Clear the content of the selected rows. The row location of the data is always different. I have tried the folowing, but it does not work. I would greatly appreciate help! Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Rows("1:65536").EntireRow.Select Selection.Clear |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Row - Select to end of sheet
firstrow = cells(rows.count,1).End(xlup)(2).row
Range(FirstRow & ":65536").ClearContents -- Regards, Tom Ogilvy "Lost in Alabama" wrote in message ... Hello! I am trying to create a section of macro to find the first row of data from the bottom of the sheet up, select one row down from the ActiveRow to the end of the sheet, then Clear the content of the selected rows. The row location of the data is always different. I have tried the folowing, but it does not work. I would greatly appreciate help! Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Rows("1:65536").EntireRow.Select Selection.Clear |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Row - Select to end of sheet
Tom, I have not seen the use of the (2) like this before.
FirstRow = Cells(Rows.Count, 1).End(xlUp)(2).Row How does this work? (1) is the last row, and (0) is next to last row?? Mike F "Tom Ogilvy" wrote in message ... firstrow = cells(rows.count,1).End(xlup)(2).row Range(FirstRow & ":65536").ClearContents -- Regards, Tom Ogilvy "Lost in Alabama" wrote in message ... Hello! I am trying to create a section of macro to find the first row of data from the bottom of the sheet up, select one row down from the ActiveRow to the end of the sheet, then Clear the content of the selected rows. The row location of the data is always different. I have tried the folowing, but it does not work. I would greatly appreciate help! Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Rows("1:65536").EntireRow.Select Selection.Clear |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Row - Select to end of sheet
It's a form of offsetting, it will take the row after the last non-empty row
found. It is similar to FirstRow = Cells(Rows.Count, 1).End(xlUp).Range("A2").Row -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Mike Fogleman" wrote in message ... Tom, I have not seen the use of the (2) like this before. FirstRow = Cells(Rows.Count, 1).End(xlUp)(2).Row How does this work? (1) is the last row, and (0) is next to last row?? Mike F "Tom Ogilvy" wrote in message ... firstrow = cells(rows.count,1).End(xlup)(2).row Range(FirstRow & ":65536").ClearContents -- Regards, Tom Ogilvy "Lost in Alabama" wrote in message ... Hello! I am trying to create a section of macro to find the first row of data from the bottom of the sheet up, select one row down from the ActiveRow to the end of the sheet, then Clear the content of the selected rows. The row location of the data is always different. I have tried the folowing, but it does not work. I would greatly appreciate help! Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Rows("1:65536").EntireRow.Select Selection.Clear |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Row - Select to end of sheet
Another way to look at it is as a shortcut for the item property
Range("B9").Item(1,1) is "B9" Range("B9").Item(2) would be "B10" See Alan Beban's little discussion on Chip Pearson's site: http://www.cpearson.com/excel/cells.htm -- Regards, Tom Ogilvy "Mike Fogleman" wrote in message ... Tom, I have not seen the use of the (2) like this before. FirstRow = Cells(Rows.Count, 1).End(xlUp)(2).Row How does this work? (1) is the last row, and (0) is next to last row?? Mike F "Tom Ogilvy" wrote in message ... firstrow = cells(rows.count,1).End(xlup)(2).row Range(FirstRow & ":65536").ClearContents -- Regards, Tom Ogilvy "Lost in Alabama" wrote in message ... Hello! I am trying to create a section of macro to find the first row of data from the bottom of the sheet up, select one row down from the ActiveRow to the end of the sheet, then Clear the content of the selected rows. The row location of the data is always different. I have tried the folowing, but it does not work. I would greatly appreciate help! Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Rows("1:65536").EntireRow.Select Selection.Clear |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Row - Select to end of sheet
Thanks Tom. Interesting discussion.
Mike F "Tom Ogilvy" wrote in message ... Another way to look at it is as a shortcut for the item property Range("B9").Item(1,1) is "B9" Range("B9").Item(2) would be "B10" See Alan Beban's little discussion on Chip Pearson's site: http://www.cpearson.com/excel/cells.htm -- Regards, Tom Ogilvy "Mike Fogleman" wrote in message ... Tom, I have not seen the use of the (2) like this before. FirstRow = Cells(Rows.Count, 1).End(xlUp)(2).Row How does this work? (1) is the last row, and (0) is next to last row?? Mike F "Tom Ogilvy" wrote in message ... firstrow = cells(rows.count,1).End(xlup)(2).row Range(FirstRow & ":65536").ClearContents -- Regards, Tom Ogilvy "Lost in Alabama" wrote in message ... Hello! I am trying to create a section of macro to find the first row of data from the bottom of the sheet up, select one row down from the ActiveRow to the end of the sheet, then Clear the content of the selected rows. The row location of the data is always different. I have tried the folowing, but it does not work. I would greatly appreciate help! Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Rows("1:65536").EntireRow.Select Selection.Clear |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Row - Select to end of sheet
You guys are so Awesome. Your knowledge is endless and it is such a Blessing
that you are willing to share it! Thanks to you all! "Mike Fogleman" wrote: Thanks Tom. Interesting discussion. Mike F "Tom Ogilvy" wrote in message ... Another way to look at it is as a shortcut for the item property Range("B9").Item(1,1) is "B9" Range("B9").Item(2) would be "B10" See Alan Beban's little discussion on Chip Pearson's site: http://www.cpearson.com/excel/cells.htm -- Regards, Tom Ogilvy "Mike Fogleman" wrote in message ... Tom, I have not seen the use of the (2) like this before. FirstRow = Cells(Rows.Count, 1).End(xlUp)(2).Row How does this work? (1) is the last row, and (0) is next to last row?? Mike F "Tom Ogilvy" wrote in message ... firstrow = cells(rows.count,1).End(xlup)(2).row Range(FirstRow & ":65536").ClearContents -- Regards, Tom Ogilvy "Lost in Alabama" wrote in message ... Hello! I am trying to create a section of macro to find the first row of data from the bottom of the sheet up, select one row down from the ActiveRow to the end of the sheet, then Clear the content of the selected rows. The row location of the data is always different. I have tried the folowing, but it does not work. I would greatly appreciate help! Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Rows("1:65536").EntireRow.Select Selection.Clear |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternate Row Select in Sheet 1 then Paste to Sheet 2 | Excel Worksheet Functions | |||
How do I select price from sheet.b where sheet.a part no = sheet.b | Excel Worksheet Functions | |||
Use of Sheet CodeNames to Select Sheet in Different Workbook | Excel Programming | |||
Macro, select Sheet "Number", NOT Sheet Name | Excel Worksheet Functions | |||
Select Sheet then Select Range | Excel Programming |