If one cell in line turn empty then entire line turns empty

**Cristy0505** · 07-18-2013, 10:53 AM

Hello everyone.

I would like to get some help.

I have a very large worksheet and I would like if, as example, if I delete content of 1 cell in specific line to entire line turn empty together.

How do I do that?

I was thinking in adding a button for "update" the worksheet before closing...

Thanks in advance.

**LaffyAffy13** · 07-18-2013, 11:04 AM

You can run a loop that checksfor empty cells and then deletes the entire row. It might look something like this:

Sub clearlines()

Dim empty as Range

For Each empty in Range("A1:A2")

If empty.FormulaR1C1 = "" Then empty.EntireRow.ClearContents

Next empty

End Sub

**Cristy0505** · 07-18-2013, 11:07 AM

Hello, thank you for your patience and attention, but if possible I would like instead of delete the row, to just clear content on rest of cells of this row...

Would it be possible?

Regards.

**LaffyAffy13** · 07-18-2013, 11:05 AM

And then add the macro to your "Update" button.

**LaffyAffy13** · 07-18-2013, 11:09 AM

Of course! Check the code, I edited it.

**Cristy0505** · 07-18-2013, 11:32 AM

Originally Posted by LaffyAffy13

Of course! Check the code, I edited it.

Thank you very much!

But how do I change R1C1 for work only in the range B4:K3000? And if not much work to ignore lines that contain formulas? (is it called formula in english? not sure...)

**LaffyAffy13** · 07-18-2013, 11:49 AM

Yes, they are called formulas. You want to clear the contents of any row that contains an empty cell, specifically in the range "B4:K3000", but ignore all rows with any cells that contain a formula? Am I understanding that correctly?

**Cristy0505** · 07-18-2013, 11:55 AM

Originally Posted by LaffyAffy13

Yes, they are called formulas. You want to clear the contents of any row that contains an empty cell, specifically in the range "B4:K3000", but ignore all rows with any cells that contain a formula? Am I understanding that correctly?

No I want macro to ignore specific cells which contain formulas. I have uploaded an sample sheet for better understanding.

Link: http://temp-share.com/show/dPf3UCA5W

In C column if you click from C4 and down you'll see there's a formula for changing suppliers name. I didn't wanted it to be changed...

Thank you again.

**LaffyAffy13** · 07-18-2013, 12:18 PM

I'm working on it, give me like 15 minutes.

**LaffyAffy13** · 07-18-2013, 12:39 PM

Alright. I noticed the code deleted your purple chart contents as well, so I have fixed that. Also, I place the formula into the cells C4 to C3000 after they have been cleared. Is this okay? Sorry it is so long, I had to find a way to save your purple chart.

Sub Botão1_Clique()

Dim vazia As Range, número_da_linha As Integer

Application.ScreenUpdating = False

número_da_linha = 4

    For Each vazia In Range("B4:B3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("B" & número_da_linha) _
            , Range("B" & número_da_linha).Offset(, 9)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("D4:D3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("D" & número_da_linha).Offset(, -2) _
            , Range("D" & número_da_linha).Offset(, 7)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("E4:E3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("E" & número_da_linha).Offset(, -3) _
            , Range("E" & número_da_linha).Offset(, 6)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("F4:F3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("F" & número_da_linha).Offset(, -4) _
            , Range("F" & número_da_linha).Offset(, 5)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("G4:G3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("G" & número_da_linha).Offset(, -5) _
            , Range("G" & número_da_linha).Offset(, 4)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("H4:H3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("H" & número_da_linha).Offset(, -6) _
            , Range("H" & número_da_linha).Offset(, 3)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("I4:I3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("I" & número_da_linha).Offset(, -7) _
            , Range("I" & número_da_linha).Offset(, 2)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("J4:J3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("J" & número_da_linha).Offset(, -8) _
            , Range("J" & número_da_linha).Offset(, 1)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("K4:K3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("K" & número_da_linha).Offset(, -9) _
            , Range("K" & número_da_linha)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

Range("C4").FormulaR1C1 = "=IF(RC[-1]=1, ""Cablena VW"", IF(RC[-1]=2, ""Cablena YH"", IF(RC[-1]=3, ""Capri"", IF(RC[-1]=4, ""Sakaguchi Industrial"", IF(RC[-1]=5, ""Metalurgica Sakaguchi"", IF(RC[-1]=6, ""Winflex"", IF(RC[-1]=7, ""Littelfuse"",IF(RC[-1]=8, ""Loopmol"", IF(RC[-1]=9, ""ITW"", IF(RC[-1]=10, ""Newtoy"", IF(RC[-1]=11, ""Kluber"")))))))))))"
Range("C4").Select
Selection.AutoFill Destination:=Range("C4:C" & número_da_linha), Type:=xlFillDefault

Application.ScreenUpdating = True

End Sub

**Cristy0505** · 07-18-2013, 01:21 PM

Oh my God! You're great!

It seemed so easy to you for making all this long code!

I would like to ask you one more favor if possible. Could you imput after the functions notes like 'here I change the cells colors and so on?

Because I really would like to learn VBA and for that I would like to understand what each thing is doing in the code.

But just if you have time.

Thank you so much!

**LaffyAffy13** · 07-18-2013, 03:26 PM

Hope that helps!

**Cristy0505** · 07-18-2013, 03:51 PM

Originally Posted by LaffyAffy13

Hope that helps!

Thank you!

I know my main question is already solved but I thought in not creating a new thread for one more question about same worksheet.

You see next to columns that should be filled with dates there are a small cell for numbers which goes like this:

If date of billing is like 9/21/13 then it should be filled with number 9 which means month 9 as mentioned on table of caption. At this moment it will be filled manually since I could not make a formula for IF in these cells because of date. Would you know by chance a easy way to automatically fill the number of month right after imputting the date on month cell?

Kind regards!

**LaffyAffy13** · 07-18-2013, 04:31 PM

Ignore this, I tried to delete it but don't know how.

**LaffyAffy13** · 07-18-2013, 03:25 PM

Sub Botão1_Clique()

Dim vazia As Range, número_da_linha As Integer

Application.ScreenUpdating = False 
'This makes you unable to see what is happening. It speeds up the process by allowing your computer to run the program without displaying anything.

número_da_linha = 4
This sets the variable equal to 4. You need the "_" to keep all of the words together and considered as one whole word, not 3 individual ones.
    
For Each vazia In Range("B4:B3000")
'This basically says "look for each range(also considered a cell) within the area 'B4:B3000'"

        If vazia.FormulaR1C1 = "" Then Range(Range("B" & número_da_linha) _
            , Range("B" & número_da_linha).Offset(, 9)).ClearContents
'This is one line of code, separated into two lines (so that you can read all of it without scrolling) by the "_". To better understand this method, search the internet.
'This code says "if the current cell's formula is blank or empty, then clear the contents of the range 'B?' to 'K?'" and the '?' is whichever number 'número_da_linha' is equal to.
        número_da_linha = número_da_linha + 1
'This raises número_da_linha from 4 to 5, 5 to 6, and so on. it is how the program clears all of the lines, but it does so one line at a time. this is why i used "Application.ScreenUpdating = False"

    Next vazia
'This pushes the path of the code back to the line "For Each vazia in Range...". It allows the code to go to cell "B?" and continue the trend until cell "B3000"

número_da_linha = 4

    For Each vazia In Range("D4:D3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("D" & número_da_linha).Offset(, -2) _
            , Range("D" & número_da_linha).Offset(, 7)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("E4:E3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("E" & número_da_linha).Offset(, -3) _
            , Range("E" & número_da_linha).Offset(, 6)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("F4:F3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("F" & número_da_linha).Offset(, -4) _
            , Range("F" & número_da_linha).Offset(, 5)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("G4:G3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("G" & número_da_linha).Offset(, -5) _
            , Range("G" & número_da_linha).Offset(, 4)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("H4:H3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("H" & número_da_linha).Offset(, -6) _
            , Range("H" & número_da_linha).Offset(, 3)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("I4:I3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("I" & número_da_linha).Offset(, -7) _
            , Range("I" & número_da_linha).Offset(, 2)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("J4:J3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("J" & número_da_linha).Offset(, -8) _
            , Range("J" & número_da_linha).Offset(, 1)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

número_da_linha = 4

    For Each vazia In Range("K4:K3000")
        If vazia.FormulaR1C1 = "" Then Range(Range("K" & número_da_linha).Offset(, -9) _
            , Range("K" & número_da_linha)).ClearContents
        número_da_linha = número_da_linha + 1
    Next vazia

Range("C4").FormulaR1C1 = "=IF(RC[-1]=1, ""Cablena VW"", IF(RC[-1]=2, ""Cablena YH"", IF(RC[-1]=3, ""Capri"", IF(RC[-1]=4, ""Sakaguchi Industrial"", IF(RC[-1]=5, ""Metalurgica Sakaguchi"", IF(RC[-1]=6, ""Winflex"", IF(RC[-1]=7, ""Littelfuse"",IF(RC[-1]=8, ""Loopmol"", IF(RC[-1]=9, ""ITW"", IF(RC[-1]=10, ""Newtoy"", IF(RC[-1]=11, ""Kluber"")))))))))))"
'This sets the value of "C4" equal to your original formula. The "RC[-1]" refers to "one column to the left of the current cell". R = rows, C = columns. If i have R[-4]C[7] that refers to "4 rows above and 7 columns to the right of the current cell"

Range("C4").Select
'selects "C4"

Selection.AutoFill Destination:=Range("C4:C" & número_da_linha), Type:=xlFillDefault
'Makes all cells in column C have the same formula and format as "C4"

Application.ScreenUpdating = True
'lets you see whats happening again

End Sub

**LaffyAffy13** · 07-18-2013, 04:28 PM

I'll see if I can figure it out.

**LaffyAffy13** · 07-18-2013, 04:58 PM

I do not think that is easily possible. Dates are not considered text, they are considered numbers. For instance, today's date is equivalent to 41473. So I can't grab anything from "41473" that will give you the current month because I do not know where excel gets these numbers from. But if you read into it, you may be able to figure it out. I am sorry that I can't help you with this. You may consider creating a new thread and asking this question there. Someone else may know how to do this. If you do, you can provide a link to this thread, but it does not match this thread's contents, so I would just create a new one and provide the same link you gave me. I hope you figure things out!

**Cristy0505** · 07-19-2013, 06:02 AM

No problem, you already helped me alot.

Thank you very much!

If one cell in line turn empty then entire line turns empty

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Re: If one cell in line turn empty then entire line turns empty

Thread Information

Users Browsing this Thread

Similar Threads

Check range A10 to A45 and if cell not empty then same line in B must have value

Need to delete the empty line

Paste in next empty line

Line chart-lot of empty cell

If value in column is yes; entire line turns red

Bookmarks

Bookmarks

Posting Permissions