Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Tuesday, 10 May 2022
  1 Replies
  5.2K Visits
0
Votes
Undo
I'm using the following code listed below. I works fine, but It wipes out the formula in one of my cells. How do I modify this code to skip one cell in my sheet?

Sub MoveRowBasedOnCellValue()
'Updated by Extendoffice 2017/11/10
Dim xRg As Range
Dim xCell As Range
Dim i As Long
Dim J As Long
Dim K As Long
i = Worksheets("Data").UsedRange.Rows.count
J = Worksheets("Completed").UsedRange.Rows.count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Completed").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Data").Range("A1:A" & i)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.count
If CStr(xRg(K).Value) = "Completed" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Completed").Range("A" & J + 1)

xRg(K).EntireRow.ClearContents


J = J + 1
End If
Next
Application.ScreenUpdating = True
Columns("A:A").Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add2 Key:=Range("A3:A16") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A2:Q16")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
Application.Goto Reference:=Worksheets("Data").Range("A3")
End With
1 year ago
·
#2711
0
Votes
Undo
Hi there,

Did you accidentally copied only part of the code that was actually working? Since the one you provided is not complete.

Anyway, to skip one cell in your sheet, you can add a string as shown in the picture below. Remember to replace the cell $A$2 with the actual cell to skip in your sheet.
skip cell.png

If you have any further questions, please don't hesitate to ask me.

Amanda
Attachments (1)
  • Page :
  • 1
There are no replies made for this post yet.