Replace bold with Blank

[jiggly]

I am looking for the proper syntax to replace any bold text with blank text...
I am currently using this:

=SUBSTITUTE('BOOK1'!G1, "some text", "")

It just replaces any instances of "some text" with nothing, and this works fine, however, i would like to make it a little more general.

Ideally, i would like to use replace any bold font with no text, but i can't figure out the syntax

=REPLACE('BOOK1'!G1.font.bold, "") or something like that

 

[jiggly]

ok, that does provide the correct output, but ran into some problems when i run this macro:

Sub foo()
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs ActiveWorkbook.FullName
Sheets("Template M2A3").Select
Application.DisplayAlerts = True
MsgBox "This may take some time, A new file will be created with todays date appended to the end. Press OK to continue"
Dim i   As Long, _
    LR  As Long
Dim X   As Long, _
    XX  As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("G" & Rows.Count).End(xlUp).Row
For i = LR To 7 Step -1
    If Len(Range("G" & i).Value) = 0 Then
        Rows(i).Delete
    End If
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
XX = Range("V" & Rows.Count).End(xlUp).Row
For X = XX To 7 Step -1
    If Len(Range("V" & X).Value) = 0 Then
        Rows(X).Delete
    End If
Next X
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & "M2A3 Hand Receipt " & Format(Now, "dd MMM yyyy")
Workbooks.Open ActiveWorkbook.Path & "\" & "Master.xlsm"
Sheets("ALL EDET").Select
MsgBox "Your file has been saved and a new file has been created and saved at : " & vbCr & vbCr & ActiveWorkbook.Path & "\" & "Hand Receipt " & Format(Now, "dd MMM yyyy") & ".xlsm"
 
Dim WkbkName As Object
Application.ScreenUpdating = False
For Each WkbkName In Application.Workbooks()
Application.DisplayAlerts = False
If WkbkName.Name = ThisWorkbook.Name Then WkbkName.Close
Next
End Sub

this code is linked to the M2A3 Hand Receipt Button on 'ALL EDET' tab btw.
I get errors on the output file that dont make sense.

 

[jiggly]

Once we updated the function, this macro seems to be causing the problem:

Public Sub DelNullRows()
Dim i   As Long, _
    LR  As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("G" & Rows.Count).End(xlUp).Row
For i = LR To 7 Step -1
    If Len(Range("G" & i).Value) = 0 Then
        Rows(i).Delete
    End If
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

it worked great before, i dont understand what has changed.
by my understanding, it checks to see if the value contained in each cell in column G is 0.... if so, it will delete it.

 

[MrKowz]

It checks to see if there is anything in column G. If there is nothing in the cell, it will delete that row.

What kind of problem is it causing?

 

[jiggly]

when i run the macro, it looks like it removes the unwanted spaces, but then #value! errors appear sporatically in column A and column G, and #ref! errors appear in column V

they are all on the same line

<TABLE style="WIDTH: 566pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=747><COLGROUP><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1206" span=2 width=33><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1206" span=4 width=33><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1206" span=15 width=33><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 150pt; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 width=198 colSpan=6>1005-01-454-0396</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 275pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 width=363 colSpan=11>Gun, Auto, 25 mm, M242A1,(19200), 12524600</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 25pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=33> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 25pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=33> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 25pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=33> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 25pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=33> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=54>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>Gun, Auto, 25 mm, M242A1 BII Consisting of:</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>#VALUE!</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>#VALUE!</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>#REF!</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>5340-01-348-6604</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>(a) Hand Crank,(19200), 12524519</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>#VALUE!</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>#VALUE!</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>#REF!</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>1005-01-121-2391</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>(b) Brush, Cleaning, Small Arms,(19200), 12524014</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>1005-01-121-2390</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>(c) Brush, Cleaning, Small Arms,(19200), 12524013</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>1005-01-119-7865</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>(d) Rod Section, Cleaning, Small Arms Consisting of:,(19200), 12524020</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>1005-01-119-7867</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>(1) Rod Section, Cleaning, Small Arms,(19200), 12524482</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>2</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>1005-01-120-0447</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>(3) Handle Assy, Cleaning Rod,(19200), 12524476</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>5340-01-370-9476</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>(e) Plug, Protective, Dust and Moisture,(19200), 12524508</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>#VALUE!</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>#VALUE!</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>#REF!</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>5825-01-526-4783</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>Navigation Set, Satellite Signals AN/PVS-1SA (DAGR) OR,(13499), 822-1873-002</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>5825-01-395-3513</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>Satellite Signals Navigation Set AN / PSN-11(V)1 (PLGR),(13499), 822-0077-103</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>5340-01-419-9928</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>Handle, Manual Control, Hydraulic Pump,(19207), 12439239</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>5830-01-434-2223</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>Intercommunication Set (80063) A3206428 AN/VIC-3(V)9,(80063), A3206428</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>#VALUE!</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>#VALUE!</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>#REF!</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 colSpan=6>5895-01-218-3962</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 colSpan=11>Installation Kit, Electronics Equipment,(80063), PPL-10132</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66>1</TD></TR></TBODY></TABLE>

 

[jiggly]

is it possibly losing it's place while looping because the data has been deleted in the worksheet?

 

[MrKowz]

is it possibly losing it's place while looping because the data has been deleted in the worksheet?

It is stepping backwards through the data, so it shouldn't be losing place. Let me play around with the file you sent me, and I'll see what I can come up with.

 

[jiggly]

OK, thanks.

 

[MrKowz]

I see what is happening.... bit complicated to explain, but when a formula uses Row() or Rows() that refer to a cell, and that reference cell is deleted, row() and rows() end up getting a #REF! error. To accommodate for this, if we change those parts of the formula to refer to a range of cells that are never deleted/inserted, this should accomplish your needs.

Make an extra sheet in your workbook, name it "R", and hide it using the VBA editor. Then change your formulas to the following:

A7: =INDEX('ALL EDET'!B:B,ROW('R'!A5))
G7: =replacebold(INDEX('ALL EDET'!F:F,ROW('R'!A5)),INDEX('ALL EDET'!A:A,ROW('R'!A5)),INDEX('ALL EDET'!C:C,ROW('R'!A5)))
V7: =INDEX('ALL EDET'!H:H,ROW('R'!A5))

 

[jiggly]

Thank you, I have no idea how you figure this stuff out...lol.

I think I have a working sheet that I can use now.


i will be glad when i put this project to rest, i am ready for vacation.

 

[MrKowz]

Thank you, I have no idea how you figure this stuff out...lol.

I think I have a working sheet that I can use now.


i will be glad when i put this project to rest, i am ready for vacation.

Glad it works out for you, and thanks for the feedback.