Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to delete last 4 rows and last 2 columns which vary
I am using Excel to open up an html file from which I then need to delete various rows and columns. It is always the first 3 rows and first column. I must always delete the last 4 row and last 2 columns but the actual row numbers and column letters can vary depending on how many rows and columns are contained in the html file. I can use Ctrl+end to go the last cell but this could vary each time the html file is downloaded. Can anyone tell me the code which would cause a macro to go to the last cell being used and delete 4 rows and 2 columns from here. Thanks Shirley -- Shirley Munro ------------------------------------------------------------------------ Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836 View this thread: http://www.excelforum.com/showthread...hreadid=401070 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to delete last 4 rows and last 2 columns which vary
Hi Shirley,
Try this Sub deleteData() Dim iLastRow As Long Dim iLastCol As Long iLastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row iLastCol = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column Columns(iLastCol - 1).Resize(, 2).Delete 'last 2 Columns(1).Delete 'first Rows(iLastRow - 3).Resize(4).Delete 'last 4 Rows(1).Resize(3).Delete 'first 3 End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Shirley Munro" wrote in message news:Shirley.Munro.1uod2b_1125569125.6012@excelfor um-nospam.com... I am using Excel to open up an html file from which I then need to delete various rows and columns. It is always the first 3 rows and first column. I must always delete the last 4 row and last 2 columns but the actual row numbers and column letters can vary depending on how many rows and columns are contained in the html file. I can use Ctrl+end to go the last cell but this could vary each time the html file is downloaded. Can anyone tell me the code which would cause a macro to go to the last cell being used and delete 4 rows and 2 columns from here. Thanks Shirley -- Shirley Munro ------------------------------------------------------------------------ Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836 View this thread: http://www.excelforum.com/showthread...hreadid=401070 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to delete last 4 rows and last 2 columns which vary
The code Bob has sent me works just fine but I am not sure how to record this using the usual Tools, Macro, Record New Macro and making it available in the Personal Macro Workbook Shirley -- Shirley Munro ------------------------------------------------------------------------ Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836 View this thread: http://www.excelforum.com/showthread...hreadid=401070 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to delete last 4 rows and last 2 columns which vary
Shirley,
Go to record a macro as normal, saving to Personal.xls, but as soon as the record starts, stop it. Then goto menu ToolsMacroMacros..., find the macro, Edit and paste that code over it. -- HTH RP (remove nothere from the email address if mailing direct) "Shirley Munro" wrote in message news:Shirley.Munro.1uoo6d_1125583528.3199@excelfor um-nospam.com... The code Bob has sent me works just fine but I am not sure how to record this using the usual Tools, Macro, Record New Macro and making it available in the Personal Macro Workbook Shirley -- Shirley Munro ------------------------------------------------------------------------ Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836 View this thread: http://www.excelforum.com/showthread...hreadid=401070 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows with more or less than 7 columns | New Users to Excel | |||
Sum of columns that vary in position | Excel Worksheet Functions | |||
Delete columns or rows | Excel Worksheet Functions | |||
subtotalling - # of columns vary | Excel Programming | |||
subtotalling where # of columns vary | Excel Programming |