Code to ignore blank or blank contains space only

[dubiousp]

Good morning my code is set to ignore blanks and move on which seems to work well, the problem occurs when the cell contains a space it trys to work with it.. I have attached code below so far any thoughts appreciated



Sub GI()

Dim row As Integer

Dim column As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim wb As Workbook
Dim print_row As Integer
Dim rs1 As Long
Dim re1 As Long
Dim cs1 As Long
Dim ce1 As Long

rs1 = Sheets("data").Range("b1").Value 'row start value set from textboxt
re1 = Sheets("data").Range("b2").Value 'row end value set from textboxt
cs1 = Sheets("data").Range("b3").Value 'col start value set from textboxt
ce1 = Sheets("data").Range("b4").Value 'col end value set from textboxt



Set wb = ThisWorkbook
Set ws1 = wb.Sheets("C&E")
Set ws2 = wb.Sheets("Print")



print_row = 13


ws2.Rows("13:100000").EntireRow.Delete

For row = rs1 To re1


For column = cs1 To ce1


'If IsEmpty(ws1.Cells(row, column).Value) = True Then
'Do nothing
If IsEmpty(ws1.Cells(row, column).Value) = True Then
'Do nothing
ElseIf IsNumeric(ws1.Cells(row, column).Value) = True Then
If (IsEmpty(ws1.Cells(row, 3).Value) = True Or IsEmpty(ws1.Cells(15, column).Value) = True) Then
'Do nothing
Else
ws2.Cells(print_row, 1).Value = ws1.Cells(row, 3).Value
ws2.Cells(print_row, 2).Value = ws1.Cells(15, column).Value
ws2.Cells(print_row, 3).Value = ws1.Cells(row, column).Value
print_row = print_row + 1
End If
ElseIf ws1.Cells(row, column).Value = ws1.Cells(17, 2).Value Then
If (IsEmpty(ws1.Cells(row, 3).Value) = True Or IsEmpty(ws1.Cells(15, column).Value) = True) Then
'Do nothing
Else
ws2.Cells(print_row, 1).Value = ws1.Cells(row, 3).Value
ws2.Cells(print_row, 2).Value = ws1.Cells(15, column).Value
ws2.Cells(print_row, 3).Value = "0"
print_row = print_row + 1
End If
End If
Next column
If IsEmpty(ws1.Cells(row, 3).Value) = True Then
'Do nothing
Else
print_row = print_row
End If
Next row

End Sub

 

[Trevor_S]

Use the Trim function to remove spaces before checking whether the cell is blank. You appear to have a lot of code lines checking for blank cells, but here's one example. You can then apply similar changes to the others.

This line:

If IsEmpty(ws1.Cells(row, 3).Value) = True Then

becomes:

If IsEmpty(Trim(ws1.Cells(row, 3).Value)) = True Then

 

[davesexcel]

You can use special cells to loop through a range of numbers.
For example:
Below will just select numbers, use the example to just loop through numbers.

Sub selectNum()
    Dim rng As Range
    Set rng = Columns("A:A").SpecialCells(xlCellTypeConstants, 1)
    rng.Select    ' or what ever you want to do with it.
End Sub

 

[dubiousp]

excellent will give both a try

 

[dubiousp]

Trim tried works very well thankyou, next thing is text that is stuck though in column not row

 

[Trevor_S]

Are you saying that you also want to ignore the cell if it contains text that is formatted as strike through? Not sure what you mean by "in column not row"?

If so, the code example is now:

If IsEmpty(Trim(ws1.Cells(row, 3).Value)) = True Or ws1.Cells(row, 3).Font.Strikethrough = True Then

 

[dubiousp]

Brillient thankyou

 

[dubiousp]

Brill, I have attached a PDF of Column


Sub GI()

Dim row As Integer

Dim column As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim wb As Workbook
Dim print_row As Integer
Dim rs1 As Long
Dim re1 As Long
Dim cs1 As Long
Dim ce1 As Long

rs1 = Sheets("data").Range("b1").Value 'row start value set from textboxt
re1 = Sheets("data").Range("b2").Value 'row end value set from textboxt
cs1 = Sheets("data").Range("b3").Value 'col start value set from textboxt
ce1 = Sheets("data").Range("b4").Value 'col end value set from textboxt



Set wb = ThisWorkbook
Set ws1 = wb.Sheets("C&E")
Set ws2 = wb.Sheets("Print")



print_row = 13


ws2.Rows("13:100000").EntireRow.Delete

For row = rs1 To re1


For column = cs1 To ce1


'If IsEmpty(ws1.Cells(row, column).Value) = True Then
'Do nothing
If IsEmpty(ws1.Cells(row, column).Value) = True Then
'Do nothing
ElseIf IsNumeric(ws1.Cells(row, column).Value) = True Then
If (IsEmpty(ws1.Cells(row, 3).Value) = True Or IsEmpty(ws1.Cells(15, column).Value) = True) Then
'Do nothing
Else
ws2.Cells(print_row, 1).Value = ws1.Cells(row, 3).Value
ws2.Cells(print_row, 2).Value = ws1.Cells(15, column).Value
ws2.Cells(print_row, 3).Value = ws1.Cells(row, column).Value
print_row = print_row + 1
End If
ElseIf ws1.Cells(row, column).Value = ws1.Cells(17, 2).Value Then
If (IsEmpty(ws1.Cells(row, 3).Value) = True Or IsEmpty(ws1.Cells(15, column).Value) = True) Then
'Do nothing
Else
ws2.Cells(print_row, 1).Value = ws1.Cells(row, 3).Value
ws2.Cells(print_row, 2).Value = ws1.Cells(15, column).Value
ws2.Cells(print_row, 3).Value = "0"
print_row = print_row + 1
End If
End If

Next column
'If IsEmpty(ws1.Cells(row, 3).Value) = True Then
'If IsEmpty(Trim(ws1.Cells(row, 3).Value)) = True Then 'ignore row with blank and space only
If IsEmpty(Trim(ws1.Cells(row, 3).Value)) = True Or ws1.Cells(row, 3).Font.Strikethrough = True Then
'Do nothing
Else
print_row = print_row
End If

Next row

 

[dubiousp]

Could I write a varible to put in row and column numbers