Deleting blank rows and shifting rows up

[edrodz86]

Okay, this is my very first post and I might not be asking the right question but here is what i'm trying to accomplish.
I'm creating a master list of projects according to employees. So I created a drop down box with employee names and then used the code:

=IF('worksheet'!C:C="employee name",'worksheet'!A:A,"")​

which copied the cell to a corresponding worksheet that each individual employee would refer to as their current projects.

My problem is that if employee A has projects on rows 1,3 and 5, then their worksheet will show projects on rows 1,3 and 5 and show rows 2 and 4 as blank.
I'm not sure if I need a macro to delete the blank rows and shift them up or some how use a code that copies their projects to the next available row.

Any help would be greatly appreciated.

 

[MUKESHY12390]

Hello...And welcome to most Amazing Excel Website...!!!

Perhaps.....this

Sub delete_blank_row()


Cells(Rows.Count, 1).End(xlUp).Select ' you can change this part "Cells(Rows.Count, 1"
Range(ActiveCell, Range("A1")).Select ' 1
Application.ScreenUpdating = False
For Each cell In Selection
If IsEmpty(cell) = True Then
cell.EntireRow.Delete
Application.ScreenUpdating = True
Range("A1").Select
End If
Next cell






End Sub

 

[edrodz86]

Thank yo so much for the quick reply, unfortunately my Excel know how is still in its beginning stages. I think what you gave me is a VBA and I think I have to enter it as a module but im not sure how to do that? Thanks again
Im using Excel 2010

 

[MUKESHY12390]

just try with new workbook...........
first enter the some text or Number in column A
and then

Select Developer Tab > Visual Basic
OR
Alt L + V 'it will open Visual Basic Editor


select Insert >Module


And paste the code I given to You..!!


Run code
Short key F5

 

[MARK858]

If I am reading this right the code from MUKESHY12390 won't delete the rows as the cells aren't empty but are rather nullstrings from the formula.
If this is the case try the code below

Sub deleteit()
    Dim LastRow As Long, i As Long
    Application.ScreenUpdating = False
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = LastRow To 1 Step -1
        If Cells(i, 1).Value = " " Then
            Cells(i, 1).EntireRow.Delete
        End If
    Next
    Application.ScreenUpdating = True
End Sub

 

[pharmerb]

Could this macro be changed to look at specific text in a given column & then delete the row? In my spreadsheet, I need to delete (with or without shift up) any row where the word "Totals" is in a cell anywhere in column C.
Thanks
pjb

Hello...And welcome to most Amazing Excel Website...!!!

Perhaps.....this

Sub delete_blank_row()


Cells(Rows.Count, 1).End(xlUp).Select ' you can change this part "Cells(Rows.Count, 1"
Range(ActiveCell, Range("A1")).Select ' 1
Application.ScreenUpdating = False
For Each cell In Selection
If IsEmpty(cell) = True Then
cell.EntireRow.Delete
Application.ScreenUpdating = True
Range("A1").Select
End If
Next cell






End Sub

 

[MARK858]

If you are trying to delete based on a word then you are better off using autofilter rather than looping

Sub DeleteIt()
    
    With Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
        .AutoFilter field:=1, Criteria1:="Totals"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Delete
        .AutoFilter
    End With

End Sub

You must have a title row (if the cells contain other words as well as Totals change "Totals" to "*Totals*"