VBA: Separate & Add Row if All Column Has Zero

unknownymous

Board Regular
Joined
Sep 19, 2017
Messages
249
Office Version
  1. 2016
Platform
  1. Windows
Hi Guys,

I'm trying to create a macro where cell that has "0" value will be separated.

CodeNameScore 1Score 2Score 3Score 4Score 5Score 6
01John989895959390
02Ben969794939390
03Sean000000
04Carl900900890
05Tim000000

<tbody>
</tbody>

End result:

CodeNameScore 1Score 2Score 3Score 4Score 5Score 6
01John989895959390
02Ben969794939390
04Carl900900890
03Sean000000
05Tim000000

<tbody>
</tbody>



Any help will be much appreciated.

Thank you!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Give this macro a try...
Code:
[table="width: 500"]
[tr]
	[td]Sub MoveZeroRows()
  Dim LastRow As Long, LastCol As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  Application.ScreenUpdating = False
  With Range(Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1))
    .Formula = "=IF(SUM(C2:" & Cells(2, LastCol).Address(0, 0) & ")=0,99999,ROW(C2))"
    .Value = .Value
    .Offset(, -LastCol).Resize(, LastCol + 1).Sort Cells(1, LastCol + 1), xlAscending
  End With
  On Error Resume Next
  Columns(LastCol + 1).Find(99999, , xlValues, xlWhole, , xlNext, , , False).EntireRow.Insert
  On Error GoTo 0
  Columns(LastCol + 1).Delete
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Hi Rick,

I noticed that whenever I add 2 blank columns in between Score 1 & Score 2, column Score 1 column with data is separated together with the zeroes. How can we modify this? Thank you.
 
Upvote 0
I noticed that whenever I add 2 blank columns in between Score 1 & Score 2, column Score 1 column with data is separated together with the zeroes. How can we modify this? Thank you.

I am not sure what you are asking for here... when I do what you describe, the results look like I would expect them to. Can you describe the problem you are seeing in more detail (perhaps post tables like you did in Message #1 )?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top