Resize issue

[christchaaya]

Hello guys.

I need to resize my activecell to delete some rows. I need to delete below ( 8 rows ) and above my activecell but I have two problems:

- How can i resize above and below my active cell in the same time ?? If it's possible.

- How can i get the first non blank cell above my activecell ?

Thank you in advance everyone

 

[ZAX]

This Code Will Assign The Address Of The First Non Blank Cell Above The Activecell To A Variant Address :

Dim MyRange As Range
Set MyRange = Range(Cells(Activecell.Row + 1, Activecell.column).Address & ":" & Cells(1, ActiveCell.Column).Address)
For Each cell In MyRange
    If cell.Value <> "" Then
       Address = cell.Address(0, 0)
       Exit For
    End If
Next

I need to resize my activecell to delete some rows. I need to delete below ( 8 rows ) and above my activecell

I don't understand what do you mean.Do you mean to delete 8 rows below the Activecell then resize the 9th row?

ZAX

 

[Dryver14]

Zax,

I think he wants to xlup from i then go back to i and down a further 8, the bit I am struggling with there is the count to where the xlup stops.

 

[Firefly2012]

Perhaps:

If Activecell.Row>1 Then
   If Activecell.Offset(-1).Value<>"" Then
       Activecell.Offset(-1).Resize(10).EntireRow.Delete
   Else
       Range(Activecell.End(xlUp),Activecell.Offset(8)).EntireRow.Delete
  End If
Else
  Activecell.Resize(9).EntireRow.Delete
End If

 

[Dryver14]

Range(Activecell.End(xlUp),Activecell.Offset(8)).EntireRow.Delete


Thats that bit answered.

 

[christchaaya]

Thank you both for your time and help .

Dryver the code works perfectly

Best regards.

 

[Dryver14]

I think thats meant for Firefly,

I only asked a question.

 

[christchaaya]

Ops :D Sorry but I'm trying to finish the code so I didn't noticed that , i thought it was a correction of his code :S Anyway thank you both guys :D

Best regards.

 

[Firefly2012]

You're welcome