Remove entire row

[Goofan]

Hi, I think this question has been asked before here but i couldn't find an "true answer" to the question i possess.

How do you remove an entire row?
my worksheet got 3 coloumns filled with text and with some that got nothing in them however i got 3 other coloumns that are below the others(exampel below). Now i want to remove those rows where there is nothing in them.

|19919191|1919191191|11116162
|blank|blank|blank
|19919191|1919191191|11116162
|19919191|1919191191|11116162
|blank|blank|blank
|19919191|1919191191|11116162
|19919191|1919191191|11116162
|blank|blank|blank
|19919191|1919191191|11116162
|19919191|1919191191|11116162
|19919191|1919191191|11116162
|blank|blank|blank
|19919191|1919191191|11116162
|19919191|1919191191|11116162
|blank|blank|blank|blank|blank|blank|blank|blank|blank|19919191|1919191191|11116162
|blank|blank|blank|blank|blank|blank|blank|blank|blank|19919191|1919191191|11116162
|blank|blank|blank|blank|blank|blank|blank|blank|blank|19919191|1919191191|11116162
|blank|blank|blank|blank|blank|blank|blank|blank|blank|19919191|1919191191|11116162

I hope this makes some sens atleast
And i need it as a function if that makes any diffrence...

Regards,
Thomas

 

[Smitty]

Welcome to the Board!

You can select the columns in question, then goto (F5)-->Special-->Blanks. Once You've verified that the correct rows have been selected you can delete them all at once (/EDR or Home-->Cells-->Delete Rows).

HTH,

 

[Goofan]

Thanks,

Well its more in the line of code im asking :D

I got a macro running and when its done i need to remove those newly created blank rows that got added during the process.

 

[Smitty]

Record a macro performing the steps I detailed and you'll have the code.

 

[Goofan]

I know what you mean but that wouldn't work as the rows differ from sheet to sheet.

The macro i got running deletes the content that once before was in the cell and adds it in the other columns cell at the end.

If you still doen't get what i mean i can send you the sheet and code(Exampel sheet tho)

 

[Smitty]

I know what you mean but that wouldn't work as the rows differ from sheet to sheet.

That shouldn't be an issue if you select the entire columns. It won't matter what's old vs. newly added as the Goto Blanks feature will just select blanks anyway.

 

[Goofan]

Well i managed to get it working thank you for the advice.

If you got the time maybe you could help me on another issue.

I want those rows that i moveed to goto a new sheet that i named Sum and to be placed at the coloumns A, B, C and downwards. the code i got is this:

Sheets.Add.Name = "Sum"
Sum.Cells(i, 1).Value = Cells(iRow, 2).Value
Sum.Cells(i, 2).Value = Cells(iCompiler, 2).Value
Sum.Cells(i, 3).Value = Cells(iRow, 3).Value

The variable i is being incremented (not just in this part of the code)
This code doesn't work i intend it todo... First of if there is a sheet allready namned sum it makes and error. (I havn't been able to solve this)

The other problem i got is that it doesnt move the rows to sum sheet (Gets an error on the "Sum."


Do you know how I could solve these issues?

Thanks in advance
Thomas

 

[Smitty]

THe first thing you need to do is test for the existence of the Sum sheet, and if it's there delete it. Here's a UDF that will tell you if it exists:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Function</SPAN> SheetExists(SheetName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <SPAN style="color:#007F00">' Returns TRUE if a sheet exists in the active workbook</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> Worksheet<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> x = ActiveWorkbook.Sheets(SheetName)<br>                <SPAN style="color:#00007F">If</SPAN> Err = 0 <SPAN style="color:#00007F">Then</SPAN> SheetExists = True _<br>                <SPAN style="color:#00007F">Else</SPAN> SheetExists = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

As for moving rows to the Sum sheet, you didn't mention what rows you want to move and how they relate to the original dataset.

 

[Goofan]

Well the rows are:
Cells(iRow, 10).Value
Cells(iCompiler, 11).Value
Cells(iRow, 12).Value


the row number will be generated to the last "not empty" row and start from there at the coloumns ("J,K,L")

Is that what you asked for?

Regards,
Thomas

 

[Smitty]

Not really. I have no idea what iRow or iCompiler are. If you just want to copy the last row to the Sum sheet, you can use something like this:

Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row

Range(Cells(LR, "J"), Cells(LR, "L")).Copy Sheets("Sum").Cells(Rows.Count, "A").End(xlUp).Offset(1)

And do you want Sum to have a continuous record of these rows (meaning you add to existing records each time), or are you going to create a new sheet each time. And if you're doing that, what's the point?