blank rows and sorting problem..plz help me...

Hi friends ..
irst of all i m very thankful to all of u for helping each other..it is my first experience to use any forum and its awesome..

i have the following problem..i have the following numbers in a column A1.
A1
1
2
3
4
5
when i delete the number suppose 2 and 4.then these numbers are deleted. but there exist a blank line ...AS after deleting 2 and 4 above numbers would be like this
1

3

5
as 2 and 4 are deleted in the above numbers...
but i want taht when i delete any number then remaining numbers should be sort automaticallly as now the remaining numbers are 1 3 5 they should be sorted as 1 2 3( because there are only three numbers)..
another thing is that i also want these blank rows should be deleted automatically.so i will get the numbers like this
1
2
3
no blank line should be there..........
any help willbe appreciated well...i will be very thankful to u people..thankssssss

You could accomplish this with a little bit of code.
Here's one way:

Sub test()

r = Range("A65536").End(xlUp).Row

For Each cell In Range("A1:A" & r)
If cell = "" Then cell.EntireRow.Delete
Next
'n = CountA(range("A:A")

r = Range("A65536").End(xlUp).Row
Range("A1").Value = 1
If Range("A2").Value = "" Then
End
Else: Range("A2").Value = 2
End If
Range("A1:A2").Select
Selection.AutoFill Destination:=Range("A1:A" & r), Type:=xlFillDefault
Range("A1").Select

End Sub


HTH,
Paul

"naughtyboy" <naughtyboy.2c8h34_1155061018.6132@excelforum-nospam.com> wrote
in message news:naughtyboy.2c8h34_1155061018.6132@excelforum-nospam.com...
>
> Hi friends ..
> irst of all i m very thankful to all of u for helping each other..it is
> my first experience to use any forum and its awesome..
>
> i have the following problem..i have the following numbers in a column
> A1.
> A1
> 1
> 2
> 3
> 4
> 5
> when i delete the number suppose 2 and 4.then these numbers are
> deleted. but there exist a blank line ...AS after deleting 2 and 4
> above numbers would be like this
> 1
>
> 3
>
> 5
> as 2 and 4 are deleted in the above numbers...
> but i want taht when i delete any number then remaining numbers should
> be sort automaticallly as now the remaining numbers are 1 3 5 they
> should be sorted as 1 2 3( because there are only three numbers)..
> another thing is that i also want these blank rows should be deleted
> automatically.so i will get the numbers like this
> 1
> 2
> 3
> no blank line should be there..........
> any help willbe appreciated well...i will be very thankful to u
> people..thankssssss
>
>
> --
> naughtyboy
> ------------------------------------------------------------------------
> naughtyboy's Profile:
> http://www.excelforum.com/member.php...o&userid=37151
> View this thread: http://www.excelforum.com/showthread...hreadid=569547
>

I forgot to mention, the code I provided will delete the entire row if the
cell in column A is blank. If you only want to delete the blank cell, then
replace the following line:
If cell = "" Then cell.EntireRow.Delete

with the line below.
If cell = "" Then cell.Delete


"PCLIVE" <pclive@livenet.net> wrote in message
news:ukhUVqxuGHA.3936@TK2MSFTNGP04.phx.gbl...
> You could accomplish this with a little bit of code.
> Here's one way:
>
> Sub test()
>
> r = Range("A65536").End(xlUp).Row
>
> For Each cell In Range("A1:A" & r)
> If cell = "" Then cell.EntireRow.Delete
> Next
> 'n = CountA(range("A:A")
>
> r = Range("A65536").End(xlUp).Row
> Range("A1").Value = 1
> If Range("A2").Value = "" Then
> End
> Else: Range("A2").Value = 2
> End If
> Range("A1:A2").Select
> Selection.AutoFill Destination:=Range("A1:A" & r), Type:=xlFillDefault
> Range("A1").Select
>
> End Sub
>
>
> HTH,
> Paul
>
> "naughtyboy" <naughtyboy.2c8h34_1155061018.6132@excelforum-nospam.com>
> wrote in message
> news:naughtyboy.2c8h34_1155061018.6132@excelforum-nospam.com...
>>
>> Hi friends ..
>> irst of all i m very thankful to all of u for helping each other..it is
>> my first experience to use any forum and its awesome..
>>
>> i have the following problem..i have the following numbers in a column
>> A1.
>> A1
>> 1
>> 2
>> 3
>> 4
>> 5
>> when i delete the number suppose 2 and 4.then these numbers are
>> deleted. but there exist a blank line ...AS after deleting 2 and 4
>> above numbers would be like this
>> 1
>>
>> 3
>>
>> 5
>> as 2 and 4 are deleted in the above numbers...
>> but i want taht when i delete any number then remaining numbers should
>> be sort automaticallly as now the remaining numbers are 1 3 5 they
>> should be sorted as 1 2 3( because there are only three numbers)..
>> another thing is that i also want these blank rows should be deleted
>> automatically.so i will get the numbers like this
>> 1
>> 2
>> 3
>> no blank line should be there..........
>> any help willbe appreciated well...i will be very thankful to u
>> people..thankssssss
>>
>>
>> --
>> naughtyboy
>> ------------------------------------------------------------------------
>> naughtyboy's Profile:
>> http://www.excelforum.com/member.php...o&userid=37151
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=569547
>>
>
>