I would like to modify that code with these specification:

I need to keep all colors in cells. But if there's no data in column G but some or all other cells in the same row have data, it will clear all data in the row but at the end of the code, if there a empty row between 2 rows with data, the one after the empty row will go up 1 row.

How can i do that?

Thanks for your help

For i = 2 To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row If i = 1 Then i = 2 End If For j = 1 To 55 If j = 7 Then GoTo nxt1 End If If Cells(i, j).Value <> "" Then Rows(i).Delete i = i - 1 GoTo nxt2 End Ifnxt1: Next jnxt2:Next i

If VBA is not a must then you can easily do this by the following method

apply data filter to the entire range data > filter > autofilter
from the dropdown in column G select blanks
select the filtered rows
delete the rows
remove data filter

Saqib

0

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

unless I completely misunderstood you this code should do what you have asked

Private Sub CommandButton1_Click()
strt = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
finis = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
For i = strt To finis Step -1
If Cells(i, 7) = "" Then Cells(i, 7).EntireRow.Delete
Next i
End Sub

Private Sub CommandButton1_Click()'loop in reverse to avoid row mismatch after deletionFor i = Cells.SpecialCells(xlCellTypeLastCell).Row To 2 Step -1 'first check if G column has data 'my understanding is that you need to delete row if: '1)Column G is empty '2)Any other column has data If Len(Trim$(Range("$G$" & i).Value)) = 0 Then 'you have 55 columns? For j = 1 To 55 'if not G Column If j <> 7 Then 'if cell is not empty If Len(Trim$(Cells(i, j).Value)) > 0 Then 'delete row and shift below row upward Rows(i).Delete Shift:=xlUp GoTo nextI End If End If Next j End IfnextI:Next iEnd Sub

all of the code are very good but the only problem that i see is that the color in cells needs to stay in all the line on that excel spreadsheet. and not delete a row.

If i have 30 rows with colors in cells, if a delete data because there's no data in column G, it will clear the data on that row and if the other row does, it will bring the data up of one line without deleting a row.

I think if you use a combination of 2 simple functions this will work very nicely. I hope this is what you need.

(NOTE: I have used [.UsedRange] here to limit the number of rows in the target sheet that I look at, for simplicity during testing, but this property is not allways reliable so you may want to replace it with another method of finding the used area / rows)

Regards,

Sub emptyMarkedRows() Dim wksTarget As Worksheet Dim rngCell As Range Dim lngRow As Long Set wksTarget = Worksheets(2) ' If there is a value in column G then clear the data in that row For lngRow = 1 To wksTarget.UsedRange.Rows.Count ' Check column G If IsEmpty(wksTarget.Cells(lngRow, 7)) Then wksTarget.Rows(lngRow).ClearContents DoEvents Next ' Delete blank rows deleteBlankRows wksTarget Set wksTarget = NothingEnd SubSub deleteBlankRows(wksTarget As Worksheet) Dim lngRow As Long Dim rngToBeDeleted As Range ' Find all blank rows For lngRow = 1 To wksTarget.UsedRange.Rows.Count If blnRangeIsBlank(wksTarget.Rows(lngRow)) Then If rngToBeDeleted Is Nothing Then Set rngToBeDeleted = wksTarget.Rows(lngRow) Else Set rngToBeDeleted = Union(rngToBeDeleted, wksTarget.Rows(lngRow)) End If End If Next ' Delete the rows rngToBeDeleted.Delete Set rngToBeDeleted = NothingEnd Sub

If i use this code:
strt = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
finis = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
For i = strt To finis Step -1

If Cells(i, 7) = "" Then
For j = i To strt
Range(Cells(j, 1), Cells(j, 55)).Value = Range(Cells(j + 1, 1), Cells(j + 1, 55)).Value
Next j
End If

I am not sure what you mean. For the given file the loop starts from row 21 and works its way to row 11 meaning in reverse order. You can fix it by setting finis to 11 instead of the given formula.

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…

The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…