0

I have a macro that performs some cleanup on values. It works fine unless there is only one row of data in the spreadsheet; if there is only one row, it blanks out my value instead of fixing it.

Sub UpdateNumberFormat()

Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Columns("AL:AM").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.NumberFormat = "General"
Range("AL2").Select
ActiveCell.FormulaR1C1 = "'"
Range("AM2").Select
ActiveCell.FormulaR1C1 = "=CONCAT(RC[-1],RC[1])"
Range("AL2:AM" & LastRow).Select
Selection.FillDown
Range("AM2:AM" & LastRow).Select
Selection.Copy
Range("AN2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("AL:AM").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select


Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.NumberFormat = "General"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"
Range("C2:C" & LastRow).Select
Selection.FillDown
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
Greg
  • 87
  • 8
  • Step 1: [Avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), Step 2: `If lastRow = 1 then exit sub`, or something related to not executing when you only have headers. – Cyril Jan 05 '22 at 15:20
  • This is frustrating because, like is so typical of Microsoft, there's always a gotcha. Why can't things just work consistently.? – Greg Jan 05 '22 at 16:41
  • If you're consistently getting the same result, then yes, it is working consistently. It may not be what you want, but it would be consistent, based on the written code. Start by removing the superfluous language, e.g., `.Select` followed by `ActiveCell`, then work on stepping through your code for when the first instance of "not what i expected" occurs. Based on your description, and noting the `2` in your Selections, I can only guess that your issues occur when there is nothing in row 2, hence I suggested the `if lastrow = 1 then exit sub` in my first comment. – Cyril Jan 05 '22 at 16:46
  • When I said "why can't things just work consistently", I was referring to my experience with Microsoft. There have been numerous times where something works consistently, but then when there's a slight condition change, then it stops working. This example code is a case in point: 99.9% of the time, I have at least two line items under the header line, but in the less than one percent of the time where there's only the header row and a single line of data, then it doesn't work as expected. That's the frustration. – Greg Jan 07 '22 at 16:01

0 Answers0