Using a macro to delete last 4 rows and last 2 columns which vary

[Shirley Munro]

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

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Shirley Munro]

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

[Bob Phillips]

Shirley,

Go to record a macro as normal, saving to Personal.xls, but as soon as the
record starts, stop it. Then goto menu Tools>Macro>Macros..., find the
macro, Edit and paste that code over it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Shirley Munro" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>