Last row capture issue

[yuvastanza]

Hello Geeks,

I need a help on the issue i am facing in excel, the excel sheet which i am using has a problem like if i give a control + down arrow from the beginning of the table which has data, it should stop at the last line of the table. However it is not stopping and crossed the table and stop at the apart several blank cells. i checked if the blank cells has data or formulas but nothing found in it. What i want is how to resolve the sheet to stop as per the excel methodology. It is disturbing my VBA code and leads to malfunctioning.

The following VBA code which i used to the action:

[code/]ActiveSheet.Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 7).Select
ActiveSheet.Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
ActiveSheet.Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 7).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Range(ActiveCell, ActiveCell.Offset(50, 0)).Select
Selection.ClearContents
[/code]
The purpose of the above code is, once the table of contents pasted in the Excel sheet, the Cursor goes to Cell A1 and comes down to the last row of the Table then it will move to the 7th column from the active cell, then filldown the formula setup in the seventh column. then it will take one step down and remove the if the formula was already existed in the blank cells.

 

[Derek Brown]

You could try the following:
https://support.office.com/en-us/ar...orksheet-e744c248-6925-4e77-9d49-4874f7474738