If column contains value do.....

[zman96]

I need some help with do an "if" statement. I want to have vba look at a column and to see whether or not there is a value in that column. If there is, remove all the blank cells above it so that it moves to the top. If there is no value then this code doesn't need to run. If not running the code is not possible to code then I can just simply spit out a random number someone in the sheet where it wont have an effect on the rest of my data.

If Range("M2:M" & last) 'contains any value 
Then 
last = Range("M:M").Find("*" , , , , 1, 2).Offset(1).Row
Range("M2:M" & last).SpecialCells(xlCellTypeBlanks).Delete xlUp
Else
'do nothing
End if

 

[Snakehips]

@zman96

Try..

Sub Shiftit()Dim MCol As Range
Dim Last As Long
Last = Cells(Rows.Count, "M").End(xlUp).Row
If Last = 1 Then Exit Sub  'no value only header in row 1? so quit
'Otherwise
Set MCol = Range("M2:M" & Last) 'contains any value
Application.ScreenUpdating = True
MCol.SpecialCells(xlCellTypeBlanks).Delete xlUp
Application.ScreenUpdating = True
End Sub

Hope that helps

 

[zman96]

@Snakehips

That works with no problems. Thank you. Could you please explain the

Application.ScreenUpdating = True

I've seen this a few times before, but do not understand why it is necessary?

Cheers

 

[zman96]

@Snakehips

Okay, I am having a problem now. So if I have data that is already up at the top, lets say A2, I get an error of "No cells were found". So I think I need vba to read if there are no blank cells above data, then also exit sub. I will show an example of the three different scenarios that could happen within my sheet.

            A                 B                  C
       January       February         March
1          1                
2          3
3          5
4          7                7
5          9                9
6          11              11
7          13              13
8          15              15
9          17              17

Column A has data in all rows with no blank cells above
Column B has a couple of blank cells above data
Column C has only blank cells

I want the final to look like:

            A                 B                  C
       January       February         March
1          1                  7
2          3                  9
3          5                  11
4          7                  13
5          9                  15
6          11                17
7          13              
8          15              
9          17

 

[zman96]

@Snakehips

Been playing with the code and added

If Range("A2").Count = 1 Then Exit Sub

right after the first

Application.ScreenUpdating = True

Do you see any problems with adding this line or complications down the road?

 

[Snakehips]

@zman96

First off, my apologies. In the original code I entered 'Application.ScreenUpdating = True' at the end, copied and pasted it in a few lines up and forgot to change tue to false.

Setting ScreenUpdating = False prevents Excel from wasting processing time and causing screen distractions by mirroring every change effected by the code.
So a code line, early on, of 'ScreenUpdating = False' is generally good coding practice. It is then important to ensure that every possible exit from the code encounters 'ScreenUpdating = Tue' so that your display can refresh to the final result and then function as normal.

The code below is revised accordingly and I have added a line such that an error caused by lack of blanks, will be ignored.

Sub Shiftit()Dim MCol As Range
Dim Last As Long
Last = Cells(Rows.Count, "M").End(xlUp).Row
If Last = 1 Then Exit Sub  'no value only header in row 1? so quit
'Otherwise
Set MCol = Range("M2:M" & Last) 'contains any value
Application.ScreenUpdating = False
On Error GoTo Done  'In case no blanks
MCol.SpecialCells(xlCellTypeBlanks).Delete xlUp
Done:
Application.ScreenUpdating = True
End Sub

 

[zman96]

@Snakehips

Thanks for clarifying that. Appreciated!