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


--
naughtyboy
------------------------------------------------------------------------
naughtyboy's Profile: http://www.excelforum.com/member.php...o&userid=37151
View this thread: http://www.excelforum.com/showthread...hreadid=569547

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

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" wrote
in message ...

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

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

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" wrote in message
...
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"
wrote in message
...

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