moving content into empty cells

[ned1234]

I am formatting sheet2 based on information in sheet1. If cell b4 is empty, I want to move the contents of the first not-empty cell in Col B up to b4 along with the information in the adjacent columns. I want to repeat the process down the page so that I eliminate empty rows.

Columns B-F are either filled or empty based on this function: =IF('Sales by Date'!$B4="w","w","").

Thank you.

 

[Jeffrey Smith]

Please try this macro. It requires you to select the range only including the column that has the "w" or no w. Instead of moving the data around, it simply deletes the row. Let me know if this works for you. The Union function allows the macro to analyze the entire section before removing rows.

Sub RemoveRows()
  
  Dim Cell As Range
  Dim U As Range, X As Long
  
  
  For Each Cell In Selection
    If Cell.Text = "" Then
      If X = 0 Then
        Set U = Cell
        X = X + 1
      Else
        Set U = Union(U, Cell)
        X = X + 1
      End If
    End If
  Next Cell
  If X > 0 Then U.EntireRow.Delete
  
End Sub

Jeff

 

[Domski]

Cross Posted here: http://www.excelforum.com/excel-new-users/775271-moving-content-into-empty-cells.html

ned1234...please read this: http://www.excelguru.ca/node/7

Dom

 

[ned1234]

That you for pointing out my faux pas - my first time trying these forums; apologies, I will mend my ways.

 

[ned1234]

Jeff - thank you taking the time to respond. I cannot eliminate entire rows because I have similar data tables arranged across the sheet; each one is assigned to a category of buyer.

 

[Jeffrey Smith]

I guess I would need more information to help further. Maybe a snapshot. It seems that you want to move data from an entire row to a new row if the "w" is missing in the formula, which is the same as deleting a row. I want to help.

Jeff

 

[ned1234]

Jeff -

Here is a start to the sheet. Hope this helps show what I am trying to do, move the data groups up the sheet so that there are not empty rows between data. First time I have done this so I hope the cut-and-paste works right:

W SKU Price Cost Profit GPM D SKU Price Cost Profit GPM
May

d TT15 470 160 310 66%
w TT04 180 102 79 44%
w TT04 180 102 79 44%
d TC68 980 305 675 69%

 

[ned1234]

No, that did not work, let me try something else

 

[Jeffrey Smith]

Can you send me a spreadsheet?

 

[ned1234]

Jeff, I'm sorry if I appear really slow, but I cannot figure out how to attach a screen shot or a spread sheet here.