I want remove blank cells column a and show result in column b
please give me a vba code
for example
remove blank.jpg
I want remove blank cells column a and show result in column b
please give me a vba code
for example
remove blank.jpg
Try this:
Sub dural() Dim N As Long, NN As Long, K As Long N = Cells(Rows.Count, "A").End(xlUp).Row K = 1 For NN = 1 To N If Cells(NN, 1) <> "" Then Cells(K, 2) = Cells(NN, 1) K = K + 1 End If Next End Sub
Last edited by Aussiebear; 01-21-2023 at 03:25 AM. Reason: Adjusted code tags
Have a Great Day!
Hi, abbccc,
if the shown are values (no formulas) you may shorten the loop to only those cells with values like
Ciao,Sub abbccc_2() Dim rngCell As Range Dim lngTarg As Long lngTarg = 1 For Each rngCell In Range("A:A").SpecialCells(xlCellTypeConstants, 23) Cells(lngTarg, 2) = rngCell lngTarg = lngTarg + 1 Next rngCell End Sub
Holger
Last edited by Aussiebear; 01-21-2023 at 03:24 AM. Reason: Adjusted code tags
Thank you HaHoBe
Thank you GarysStudent
Or non-looper [frequently shown by snb]
Option Explicit Public Sub GetNonBlanks() Dim r As Range Dim v As Variant Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) v = Filter(Application.Transpose(Evaluate("=IF(" & r.Address & "<>""""," & r.Address & ",""~"")")), "~", False) Range("B1").Resize(UBound(v) + 1, 1).Value = Application.Transpose(v) End Sub
Last edited by Aussiebear; 01-21-2023 at 03:24 AM. Reason: Adjusted code tags
Regards,
--------------------------------------------------------------------------------------------------------
Shrivallabha
--------------------------------------------------------------------------------------------------------
Using Excel 2016 in Home / 2010 in Office
--------------------------------------------------------------------------------------------------------
no error checking and as is will only work with constant cells but both could easily be added
Sub test() Call Columns("A:A").SpecialCells(xlCellTypeConstants, 23).Copy(Range("B1")) Application.CutCopyMode = False End Sub
Paul
Last edited by Aussiebear; 01-21-2023 at 03:23 AM. Reason: Adjusted code tags
orSub M_snb002() With Columns(1) .AutoFilter 1, "<>" .Cut Cells(1, 2) .AutoFilter End With End Sub
Sub M_snb003() Columns(1).SpecialCells(4).EntireRow.Delete Columns(1).Insert End Sub
Last edited by Aussiebear; 01-21-2023 at 03:23 AM. Reason: Adjusted code tags
Thank you for all
Is the VBA code provided in the solution worked for all the latest as well as old versions of MS Excel?