Dim i As Long, j As Long
Sub Sample()
Dim lastrow As Long
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
For j = 1 To lastrow
On Error Resume Next
If OnlyNumbers(Sheets("Sheet1").Range("A" & j).Value) = False Then _
Sheets("Sheet1").Range("A" & j).ClearContents
On Error GoTo 0
Next
For j = lastrow To 1 Step -1
If IsNumeric(Sheets("Sheet1").Range("A" & j)) And _
Len(Trim(Sheets("Sheet1").Range("A" & j + 1).Value)) = 0 Then
Sheets("Sheet1").Range("A" & j + 1) = Sheets("Sheet1").Range("A" & j)
End If
Next
End Sub
Function OnlyNumbers(strInput As String) As Boolean
Dim strChar As String, strOutput As String
OnlyNumbers = True
strOutput = ""
For i = 1 To Len(strInput)
strChar = Mid(strInput, i, 1)
If Not (IsNumeric(strChar)) Then
OnlyNumbers = False
Exit Function
End If
Next i
End Function
CRXIuser2005-Datafile-2.xlsm
Dim i As Long, j As Long
Sub Sample()
Dim lastrow As Long
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
For j = 1 To lastrow
On Error Resume Next
If OnlyNumbers(Sheets("Sheet1").Range("A" & j).Value) = False Then _
Sheets("Sheet1").Range("A" & j).ClearContents
On Error GoTo 0
Next
For j = 10 To lastrow - 1
If Len(Trim(Sheets("Sheet1").Range("A" & j + 1).Value)) = 0 Then _
Sheets("Sheet1").Range("A" & j + 1).Value = Sheets("Sheet1").Range("A" & j).Value
Next
End Sub
Function OnlyNumbers(strInput As String) As Boolean
Dim strChar As String, strOutput As String
OnlyNumbers = True
strOutput = ""
For i = 1 To Len(strInput)
strChar = Mid(strInput, i, 1)
If Not (IsNumeric(strChar)) Then
OnlyNumbers = False
Exit Function
End If
Next i
End Function
CRXIuser2005-Datafile-2.xlsm
Title | # Comments | Views | Activity |
---|---|---|---|
Please help with my first pivot table | 12 | 37 | |
How can I sort the data shown in Sheet 1 and copy it to Sheet 2? | 8 | 19 | |
Importing csv to excel - text formating | 14 | 20 | |
copying horizontal cells values vertically in Excel | 2 | 0 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
11 Experts available now in Live!