CODEWITHSUNDEEP
excelvbaexcel-formula
Frost10000
Frost10000

Reputation: 21

Using IsEmpty and Merging empty cells

I'm writing code that searches through a very large excel sheet with a lot of duplicates, I can easily sort the duplicates together as they all have 1 matching column, and ID column. The thing that I am stuck on is why IsEmpty isn't working for looking at the current cell and seeing if it's empty and if the next cell (the duplicate) has data. Then the cell with data would merge over into the cell that's empty.

I've tried using Range as the argument as well as cells to no avail.

Private Sub CountDuplicates()
Dim i As Integer

'The cell it will use to search
Dim idCheck As Range

'The cell it will use to compare text
Dim currentCell As Range

'This will be used to format the "Changes" column
Dim rowNumberValue As Integer, columnNumberValue As Integer, rowBelow As Integer
placement = 0
colNum = 3

rowNumberValue = ActiveCell.Row
columnNumberValue = ActiveCell.Column
rowBelow = ActiveCell.Row + 1

'Searches by ID column
For Each idCheck In Worksheets("Sheet1").Range("B2:B1000")

   'This checks to find duplicate ID rows
   If idCheck.Value = idCheck.Offset(-1, 0).Value Then

         'Goes from each column starting from the ID column (H = 7th letter in alphabet and H is the last column)
         For colNum = 3 To 7

            'Checks to see if the cell has no value but the duplicate does
            If IsEmpty(Range(Cells(rowNumberValue, colNum))) = True And IsEmpty(Range(Cells(rowNumberValue + 1, colNum))) = False Then
               Range(Cells(rowNumberValue, colNum), Cells(rowBelow, colNum)).Merge

So ideally, if a row has one cell missing data, but the other row has it, then merge/copy the data into the cell with missing data.

Upvotes: 0

Views: 306

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Replace tests like:

IsEmpty(Range(Cells(rowNumberValue, colNum))) = True

with:

Cells(rowNumberValue, colNum) = ""

Upvotes: 1

Related Questions