Finding 1st Blank Cell in a Column, and then Copying data in adjacent columns

[MB95]

Hello, will start off by saying I am extremely new to VBA so I appreciate the patience beforehand.

1) I am currently attempting to search "Column A" to find the first blank cell row (highlighted red) in "Column A" which then has data in the next 5 columns (highlighted light red).

2) I am then attempting to take the data in "Columns B:F" and paste/offset into the destination marked with the purple borders.

This below screenshot shows an example of what I am working with on a smaller scale as my original file is thousands rows long.

My current code enables me to find the blank cells in "Column A" (highlighted blue) but I am at a loss to begin figuring out how to copy the data adjacent to it.

Sub test2()

Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String

sourceCol = 1

rowCount = cells(Rows.Count, sourceCol).End(xlUp).Row

    For currentRow = 1 To rowCount
        currentRowValue = cells(currentRow, sourceCol).Value
        If IsEmpty(currentRowValue) Or currentRowValue = "" Then
            cells(currentRow, sourceCol).Interior.ColorIndex = "17"
        End If
    
    Next
    
End Sub

Thanks!

 

[Peter_SSs]

I think I may have figure it out with the below...

Glad you were able to get it going.

A few comments though.

vbNullString is the same as "" so you were double-testing those cells unnecessarily
Since you already tested c for being 'blank' there is no need to test it again.
So your code could do just as well without these parts

Sub Rearrange()
  Dim rBlank As Range, c As Range
  Set rBlank = Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row)
  Application.ScreenUpdating = False
  For Each c In rBlank
    If Trim(c) = vbNullString Or c = "" Then
        c.Offset(-1, 7).Resize(, 5).Value = c.Offset(, 1).Resize(, 5).Value
        c.Offset(-1, 7).Resize(, 5).Interior.ColorIndex = 36
    End If
    If Trim(c) = vbNullString Or c = "" Then
        c.EntireRow.Delete
    End If
  Next c
  Columns("K").NumberFormat = "mm/dd/yyyy"
  Application.ScreenUpdating = True
End Sub

As a matter of interest, if the numbers in column A are actual numbers and not text values, this would have been another way to attack it.

Sub Rearrange_v2()
  Dim rA As Range
  
  Application.ScreenUpdating = False
  For Each rA In Columns("A").SpecialCells(xlConstants, xlNumbers).Areas
    With rA.Cells(rA.Rows.Count, 8).Resize(, 5)
      .Value = rA.Offset(rA.Rows.Count, 1).Resize(, 5).Value
      .Interior.ColorIndex = 36
      .Offset(1).EntireRow.Delete
    End With
  Next rA
  Columns("K").NumberFormat = "mm/dd/yyyy"
  Application.ScreenUpdating = True
End Sub

 

[MB95]

@johnnyL @Peter_SSs

Hello, I had a follow-up here that is relevant to the explanations above. In the example below features a chunk of a larger dataset & I am attempting to again offset data a little differently.

In Rows 3, 8, & 13 I am attempting to line up as shown in the before & after below.

Before:

After:

My initial thoughts were to use the code below:

Sub Rearrange2()
  Dim rBlank_ As Range, c_ As Range
  Set rBlank_ = Range("K1:K" & Range("L" & Rows.Count).End(xlUp).Row)
  Application.ScreenUpdating = False
  For Each c_ In rBlank_
    If Trim(c_) = vbNullString Or c_ = "" Then
        c_.Offset(, -3).Resize(, 5).Value = c_.Offset(, 1).Resize(, 5).Value
        c_.Offset(, -3).Resize(, 5).Interior.ColorIndex = "38"
    End If
    Next c_
   Application.ScreenUpdating = True
End Sub

This works just fine up until "Row 8" of the data shown above & I understand this is because data I am trying to offset in this row begins in "Column M" & not in "Column L" like "Row 3".

All in, I am trying to figure out how to get a code that can offset but also account for data in rows that follow where the string may start a Column over than the one previous to it.

After my code:

Thanks again!

 

[Peter_SSs]

What about this?

Sub Move_Left()
  Columns("H:L").SpecialCells(xlBlanks).Delete Shift:=xlToLeft
End Sub