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

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

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

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